---
title: "Central Bank Exchange Rates in Power BI: The Official-Rate Setup (2026)"
description: "How to pull official ECB, Fed, and 33 other central bank exchange rates into Power BI with Power Query — the M code, scheduled refresh, historical backfill, and why finance reports should not run on market rates."
canonical: https://allratestoday.com/blog/central-bank-exchange-rates-power-bi/
source: AllRatesToday (https://allratestoday.com/) — Markdown mirror of the page above; index of all pages at https://allratestoday.com/llms.txt
generated: 2026-09-22T21:07:13.047Z
---

![Business intelligence dashboard with charts](https://images.pexels.com/photos/38808473/pexels-photo-38808473.jpeg?auto=compress&cs=tinysrgb&dpr=2&h=650&w=940)

We previously covered [live market rates in Power BI](https://allratestoday.com/blog/best-currency-api-for-power-bi-2026/) — 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 116 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 116 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.

**Why the RateDate column is the point:** every row carries the bank's own publication date. When someone asks in November why the September report used 1.0842, the answer is in the dataset — "ECB reference rate, published 2026-09-30" — not in a screenshot someone hopefully took.

## 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](https://allratestoday.com/pricing/); 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](https://allratestoday.com/central-bank-rates-api/).

## 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](https://allratestoday.com/blog/best-central-bank-exchange-rate-api-2026/): 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.

## Official rates, one Power BI query

116 central banks in one response shape. Latest table free — no credit card.

 [Get your free API key](https://allratestoday.com/register/)

## Related Articles

- [Best currency API for Power BI (market rates)](https://allratestoday.com/blog/best-currency-api-for-power-bi-2026/)
- [7 best central bank exchange rate APIs in 2026](https://allratestoday.com/blog/best-central-bank-exchange-rate-api-2026/)
- [Exchange rates in Excel](https://allratestoday.com/blog/excel-exchange-rates/)
- [Official vs market exchange rates: which does your use case need?](https://allratestoday.com/blog/official-exchange-rate-vs-market-rate/)
- [All covered central banks — product page](https://allratestoday.com/central-bank-rates-api/)