Central Bank Exchange Rates in Power BI: The Official-Rate Setup (2026)

We previously covered live market rates in Power BI — the right feed for a sales dashboard. This post is for the other Power BI workload: the finance report. Month-end FX revaluation, consolidated group reporting, intercompany invoicing, anything an auditor might open — those must use an official rate, fixed once per publication, reproducible long after the refresh ran. A live feed that returns a different number every refresh is precisely wrong for this: rerun last month's report and the figures move.

Below is the complete setup: one Power Query function that reads any of 35 central banks, a merge pattern for per-invoice-date rates, and the refresh schedule that matches how banks actually publish.

Step 1 — a query for the latest published table

In Power BI Desktop: Get Data → Blank Query → Advanced Editor, then paste:

let
    Source = Json.Document(
        Web.Contents(
            "https://allratestoday.com",
            [
                RelativePath = "api/v1/central-bank/ecb/latest",
                Headers = [Authorization = "Bearer art_live_YOUR_KEY"]
            ]
        )
    ),
    rates = Source[rates],
    ToTable = Table.FromList(rates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Expanded = Table.ExpandRecordColumn(
        ToTable, "Column1",
        {"base_ccy", "quote_ccy", "rate_type", "value", "rate_date"},
        {"Base", "Quote", "Type", "Rate", "RateDate"}
    ),
    Typed = Table.TransformColumnTypes(
        Expanded,
        {{"Rate", type number}, {"RateDate", type date}}
    )
in
    Typed

Swap ecb for fed, boe, snb, banxico — same query, same columns, any of the 35 banks. Using RelativePath (rather than concatenating the URL) matters: it is what keeps scheduled refresh working in the Power BI service without a dynamic-data-source error.

Step 2 — make it a function, parameterised by bank

(bank as text) =>
let
    Source = Json.Document(
        Web.Contents(
            "https://allratestoday.com",
            [
                RelativePath = "api/v1/central-bank/" & bank & "/latest",
                Headers = [Authorization = "Bearer art_live_YOUR_KEY"]
            ]
        )
    )
in
    Source

Name it fnBankRates, then build a one-column table of bank codes (ecb, fed, boe…) and add an invoked column. One refresh now lands every bank your group reports against, in one appended table — because the response shape is identical across banks, the expansion logic is written once.

Step 3 — rates for each invoice date

For revaluation you rarely want today's rate; you want the rate for each transaction date. Pull a time series once and merge locally instead of calling per row:

let
    Source = Json.Document(
        Web.Contents(
            "https://allratestoday.com",
            [
                RelativePath = "api/v1/central-bank/ecb/history",
                Query = [source = "EUR", target = "USD", from = "2026-01-01", to = "2026-06-30"],
                Headers = [Authorization = "Bearer art_live_YOUR_KEY"]
            ]
        )
    )
in
    Source

Expand to a Date/Rate table, then Merge Queries against your invoice table on the date column. Weekends and bank holidays are the classic trap here — an invoice dated Saturday has no published rate. The API resolves those to the most recent published date and flags them, which is exactly the fallback most accounting policies specify in words but analysts must implement by hand.

Historical dates and time series need a paid plan; the latest table used in steps 1–2 is on the free tier (300 requests/month).

Step 4 — schedule refresh around publication, not midnight

Banks publish at known local times: the ECB around 16:00 CET, the Bank of England mid-afternoon London, the Fed's H.10 weekly on Monday afternoons US time, HMRC monthly. A dataset that refreshes at 00:00 shows yesterday's table all day and refreshes again into the same data. Schedule after your slowest bank's publication — for a European book, one refresh at 17:30 CET covers the ECB, SNB, and the Nordics in a single pass. Each bank's cadence is listed on its product page.

Why not scrape the banks from Power Query directly?

You can — Power Query will read the ECB's XML and the BoE's CSV endpoints. Teams that do this end up owning one query per bank, each with its own parsing, its own holiday logic, and its own silent breakage when a portal changes (the M error surfaces at refresh time, in production, to a finance user). The economics are the same as in our central bank API comparison: one bank direct is fine; a portfolio of banks is a maintenance job that JSON-with-one-shape makes disappear.

FAQ

How do I get ECB rates into Power BI?

Json.Document over Web.Contents against the /central-bank/ecb/latest endpoint with your key in the Authorization header — the full M is in step 1. Expand rates, type the columns, done.

Will scheduled refresh work in the Power BI service?

Yes, provided the query uses RelativePath/Query options rather than string-concatenated URLs, and the credential is set to Anonymous (the API key travels in the header). This is the standard pattern for keyed JSON APIs in Power BI.

Should I use the official rate or the market rate in reports?

Dashboards: market rate. Anything accounting-adjacent: the official rate your policy names, because it is fixed and reproducible. Many groups need both — one dataset per feed keeps the audit boundary clean.