Power BI Currency Conversion: Live Exchange Rates via API (2026 Guide)

Every guide to Power BI currency conversion falls into one of two camps. The DAX camp shows you an elegant measure and quietly assumes an ExchangeRate table already exists in your model. The API camp shows you a Power Query snippet that works beautifully in Power BI Desktop and then fails the moment you publish, because scheduled refresh rejects it.

This guide covers both halves, in order: how to pull live exchange rates into Power BI from a REST API in a way that survives publishing to the Power BI Service, how to model those rates so your numbers are defensible, and how to convert amounts either at import time or at query time depending on what your report actually needs.

Every Power Query and DAX sample below was written against the documented Finexly API response shapes.

First Decide Which Conversion Problem You Have

"Currency conversion in Power BI" is three different engineering problems wearing the same name, and picking the wrong one is the most expensive mistake in this whole article.

1. Many source currencies, one reporting currency. Your sales table has EUR, GBP and JPY rows and the CFO wants one USD number. Convert at import time. The rate is a property of the transaction, not of the report.
2. One source currency, many reporting currencies. Everything is stored in USD and users pick a display currency from a slicer. Convert at query time in DAX. Pre-computing every currency is impractical.
3. Many source currencies, many reporting currencies. Normalise to a single pivot currency at import, then apply case 2 on top. Do not try to solve this in one step.

The rule of thumb that experienced modellers repeat, and that is worth repeating again: apply the conversion as early as you can get away with. Every conversion you push to query time costs you at every visual, every filter change, every slicer click.

Pulling Live Rates Into Power Query the Right Way

Start with the rate table. The single most efficient call is a multi-pair lookup, so you get every currency you care about in one request instead of one request per currency.

curl -H "Authorization: Bearer YOUR_API_KEY" \
  "https://api.finexly.com/v1/convert?q=USD_EUR,USD_GBP,USD_JPY"

{
  "USD_EUR": { "rate": 0.9215 },
  "USD_GBP": { "rate": 0.7892 }
}

Here is the equivalent as a Power Query M query. Create a blank query (Home → New Source → Blank Query → Advanced Editor) and paste:

let
    ApiKey = "YOUR_API_KEY",
    Pairs  = "USD_EUR,USD_GBP,USD_JPY,USD_CAD,USD_AUD,USD_CHF",
    Source = Json.Document(
        Web.Contents(
            "https://api.finexly.com",
            [
                RelativePath = "v1/convert",
                Query        = [ q = Pairs ],
                Headers      = [ #"Authorization" = "Bearer " & ApiKey ]
            ]
        )
    ),
    ToTable  = Record.ToTable(Source),
    Expanded = Table.ExpandRecordColumn(ToTable, "Value", {"rate"}, {"Rate"}),
    Split    = Table.SplitColumn(
                   Expanded, "Name",
                   Splitter.SplitTextByDelimiter("_", QuoteStyle.None),
                   {"BaseCurrency", "Currency"}
               ),
    Typed    = Table.TransformColumnTypes(
                   Split,
                   {{"BaseCurrency", type text}, {"Currency", type text}, {"Rate", type number}}
               ),
    Stamped  = Table.AddColumn(Typed, "RetrievedAt", each DateTimeZone.UtcNow(), type datetimezone)
in
    Stamped

Name it FxRates. You get a four-column table — BaseCurrency, Currency, Rate, RetrievedAt — that is immediately usable in relationships and measures.

The Web.Contents Mistake That Breaks Scheduled Refresh

Notice what the query above does not do: it never concatenates the parameters into the URL string. This is the single most common reason a Power BI currency conversion report works on the desktop and dies in the cloud.

If you write this instead:

// Do NOT do this
Source = Json.Document(
    Web.Contents("https://api.finexly.com/v1/convert?q=" & Pairs)
)

…Power BI Desktop will happily refresh it, and the Power BI Service will refuse it with "This dataset includes a dynamic data source. Refresh is not supported." The service needs to resolve a static base URL at analysis time so it can attach credentials to it. Passing the variable parts through RelativePath and Query gives it exactly that: the base stays https://api.finexly.com, and everything dynamic lives in options.

The same applies to any query that builds a URL from a parameter, a date, or a value from another table. If you take one thing from this article, take RelativePath.

Handling the API Key Without Hardcoding It

ApiKey = "YOUR_API_KEY" inline is fine for a five-minute test and wrong for anything shared. The M code of a semantic model is visible to anyone with build permission on it.

Two workable options:

• A Power Query parameter (Home → Manage Parameters), referenced as ApiKey = KeyParam. It is still stored with the model, but it is centralised and easy to rotate, and you can override it per environment with deployment pipelines.
• The Web API credential type. In Power BI Service, go to Semantic model settings → Data source credentials → Edit credentials and choose Web API, supplying the key there. The service then injects the Authorization header itself and you drop the Headers option from your M entirely. This keeps the secret out of the model definition.

Whichever you choose, use a key scoped to reporting only, and rotate it when someone leaves. If the key ends up in a URL query string rather than a header it can appear in proxy and server access logs — the Finexly docs call this out, and it applies to every provider.

Building a Currency Dimension, Not Just a Rate List

A rate table alone gives you numbers. It does not give you correct labels, correct decimal places, or a slicer that sorts sensibly. Pull the currency list as its own dimension:

let
    ApiKey = "YOUR_API_KEY",
    Source = Json.Document(
        Web.Contents(
            "https://api.finexly.com",
            [
                RelativePath = "v1/currencies",
                Headers      = [ #"Authorization" = "Bearer " & ApiKey ]
            ]
        )
    ),
    ToTable = Table.FromList(Source, Splitter.SplitByNothing(), {"Currency"}),
    Typed   = Table.TransformColumnTypes(ToTable, {{"Currency", type text}})
in
    Typed

Then add the two columns Power BI cannot infer for you:

• MinorUnits — the number of decimal places the currency actually uses. JPY has 0, KWD has 3, most have 2. Formatting a yen total to two decimals is a visible correctness bug in a finance report, and rounding at the wrong step compounds it. The currency rounding guide covers where the error creeps in.
• FormatString — e.g. "\€#,0.00", "\¥#,0". You will need this for dynamic format strings later.

Both of these follow the ISO 4217 standard rather than anything Power BI knows natively; the ISO 4217 reference has the full table. Mark this table as a dimension, relate it to FxRates[Currency] one-to-many, and use it — not the rate table — as the source of your slicer.

Path A: Convert at Import Time (Fast, Boring, Correct)

For scenario 1 — many currencies in, one currency out — do the work in Power Query and let the model store a single clean number.

1. Load your transactions query into Power Query.
2. Home → Merge Queries, joining Transactions[Currency] to FxRates[Currency] (left outer).
3. Expand the merged column and keep Rate.
4. Add Column → Custom Column:

= if [Currency] = "USD" then [Amount]
  else if [Rate] = null then null
  else [Amount] / [Rate]

Note the null branch. A left outer join against a rate table that is missing a currency produces null, and null in Power Query arithmetic silently produces null rather than an error — which becomes a blank in your visual and a total that is quietly too small. Make the gap explicit so you can filter for it and see it.

Note also the division. USD_EUR = 0.9215 means one USD buys 0.9215 EUR, so converting a EUR amount into USD divides. Converting a USD amount into EUR multiplies. Getting this backwards is the second most common bug in multi-currency reports, and at rates near 1.0 it is nearly invisible — a 3% error in a EUR/USD figure looks like a rounding difference until someone reconciles it.

Path B: Convert at Query Time with DAX

For scenario 2 — one stored currency, a user-selected display currency — the conversion has to happen in a measure.

The naive version does a LOOKUPVALUE per row and is slow on anything past a few hundred thousand rows. Aggregate first, convert once:

Sales (Reporting Currency) =
VAR SelectedCurrency = SELECTEDVALUE ( Currency[Currency], "USD" )
VAR Rate =
    CALCULATE (
        SELECTEDVALUE ( FxRates[Rate] ),
        FxRates[Currency] = SelectedCurrency
    )
VAR Result =
    IF (
        SelectedCurrency = "USD",
        [Sales Amount],
        [Sales Amount] * Rate
    )
RETURN
    IF ( ISBLANK ( Rate ) && SelectedCurrency <> "USD", BLANK (), Result )

Two details that matter more than they look:

• SELECTEDVALUE with a default. Without the "USD" fallback, the measure returns blank whenever no currency is selected, which is the state your report loads in.
• The explicit blank guard. If a currency has no rate, return blank deliberately rather than letting [Sales Amount] * BLANK() return zero. A zero in a revenue card is a lie; a blank is a visible gap.

When the Rate Varies Over Time

The measure above uses a single current rate for the entire dataset. That is correct for "what would last year's revenue be worth today" and wrong for almost everything else. If your rate table has one row per currency per day, group by date before converting:

Sales (Historical Rates) =
SUMX (
    VALUES ( 'Date'[Date] ),
    VAR DayRate =
        CALCULATE (
            SELECTEDVALUE ( FxRates[Rate] ),
            FxRates[Currency] = SELECTEDVALUE ( Currency[Currency], "USD" )
        )
    RETURN
        [Sales Amount] * DayRate
)

Iterating over VALUES('Date'[Date]) rather than the fact table keeps the iterator small — days, not transactions.

Dynamic Format Strings

A converted number with a hardcoded $ prefix is worse than no symbol at all. In Power BI, set the measure's Format to Dynamic and supply an expression:

SELECTEDVALUE ( Currency[FormatString], "#,0.00" )

Now the card that shows ¥ shows ¥, with zero decimals, without a second measure. This used to require calculation groups in Analysis Services; dynamic format strings for measures brought it into Power BI proper.

Which Rate Should You Actually Be Using?

This is the question that separates a dashboard from a report finance will sign off on, and no API can answer it for you.

• Transaction-date spot rate — for recording individual transactions. Highest fidelity, largest rate table.
• Monthly or period average — the standard for income-statement items under both IAS 21 and ASC 830. It smooths intra-month volatility and is what most consolidations actually use.
• Period-closing rate — for balance-sheet items: cash, receivables, payables.
• Budget or plan rate — a fixed rate held constant all year so that variance analysis isolates operational performance from FX movement.

A serious model often needs two or three of these side by side, as separate columns on the same rate table (SpotRate, AverageRate, ClosingRate) rather than separate tables. If your report feeds anything that ends up in a filing, the exchange rates and tax reporting guide covers which source and timestamp you need to be able to defend, and the historical exchange rates guide covers retrieving dated rates rather than live ones.

The Weekend Gap That Silently Breaks Totals

FX markets close. A daily rate table built from a live API has no Saturday, no Sunday, and no 25 December. Join a transaction dated Saturday to that table and you get null, and null becomes a blank, and the blank becomes a total that is too small by exactly the weekend's worth of sales.

Fix it in the rate table, not in the measure. Generate a complete date list and forward-fill:

let
    Dates = List.Dates(#date(2026,1,1), Duration.Days(Date.From(DateTime.LocalNow()) - #date(2026,1,1)) + 1, #duration(1,0,0,0)),
    DateTable = Table.FromList(Dates, Splitter.SplitByNothing(), {"Date"}),
    Typed = Table.TransformColumnTypes(DateTable, {{"Date", type date}}),
    Joined = Table.NestedJoin(Typed, {"Date"}, RateHistory, {"Date"}, "r", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Joined, "r", {"Currency", "Rate"}),
    Filled = Table.FillDown(Expanded, {"Currency", "Rate"})
in
    Filled

Table.FillDown carries Friday's rate across the weekend, which is the conventional treatment and, importantly, a stated treatment rather than an accidental one. Sort by currency and date before filling, or you will carry the wrong currency's rate across the gap.

If your plan does not include historical endpoints, you can build history forward instead: append today's rates to a stored table on each refresh — a Dataflow or a Fabric Lakehouse table works well — and after a quarter you have a real time series. It is not retroactive, but it costs one API call a day.

Refresh Scheduling and the Quota Arithmetic

Power BI Pro allows 8 scheduled refreshes per day on a semantic model; Premium and Fabric capacities allow 48. That is the number your API quota has to cover, and the arithmetic is friendlier than most people assume.

The rate table above is two calls per refresh: one to /v1/currencies, one to /v1/convert. So:

The 10-per-minute ceiling is the one to watch. If you build a query that calls /v1/rate once per currency inside a Table.AddColumn, twenty currencies means twenty calls in a couple of seconds and a burst of 429 responses mid-refresh. That is precisely why the multi-pair /v1/convert call exists. Batch, and cache: the caching and error handling guide covers retry-with-backoff patterns that apply equally to a scheduled refresh.

Gateways, Excel and Fabric

A few environment notes that save an afternoon each:

• No gateway required. A cloud REST API is not an on-premises source, so you do not need an on-premises data gateway for this. If refresh fails and someone suggests installing one, that is almost always the dynamic-data-source error in disguise.
• Excel uses the same engine. Power Query in Excel accepts the exact M above. If your audience lives in workbooks rather than dashboards, the live exchange rates in Excel guide covers WEBSERVICE, LAMBDA and the version matrix; there is a Google Sheets equivalent too.
• Fabric Dataflow Gen2 is the better home for the rate table once more than one report needs it. Land rates once, let every semantic model read the same table, and your API usage stops scaling with the number of reports.
• Sanity-check against a known figure before you publish. Pull one pair from the currency converter and compare it to what your model shows for the same pair on the same timestamp. If they disagree, you have a direction or a rounding problem, and you want to find it now rather than in a board meeting.

Frequently Asked Questions

Can Power BI convert currencies without an API? It can, if you supply the rates yourself — a manually maintained table, a finance system export, or a database view. Power BI has no built-in FX rate source. An API matters when you need rates that update without someone remembering to update them.

Why does my currency conversion report refresh in Power BI Desktop but fail in the Service? Almost always the dynamic data source error. Your Web.Contents call builds its URL by string concatenation. Move the variable parts into the RelativePath and Query options so the base URL is static, republish, and re-enter the credentials.

Should I convert currency in Power Query or in DAX? Power Query when the report has one reporting currency — it is faster and simpler. DAX when users choose the currency at runtime. If you need both, normalise to one pivot currency in Power Query and layer the DAX measure on top.

How many API requests will a Power BI refresh use? Two per refresh if you batch every pair into a single /v1/convert call. At the Power BI Pro maximum of 8 refreshes a day that is roughly 480 requests a month, inside a free plan. It only gets expensive if you call the API once per currency or once per row.

How do I handle weekends and public holidays in a daily rate table? Generate a continuous date table, left-join the rates to it, sort by currency and date, then forward-fill. Friday's rate carries through the weekend. The important part is that the treatment is deliberate and documented, not that the rows silently go missing.

Which exchange rate should I use for financial reporting? Period-average rates for income-statement items, closing rates for balance-sheet items, under both IAS 21 and ASC 830. Store them as separate columns on one rate table so a report can switch between them without a model change.

Ready to put live rates behind your dashboards? Get your free Finexly API key — no credit card required. Start with 1,000 requests per month, which is enough to refresh a Power BI Pro workspace at its maximum rate, and upgrade when you need historical data or a faster cadence. If you are still weighing providers, the comparison page puts the options side by side.