How to Get Live Exchange Rates in Power BI: Power Query, DAX and the Refresh Trap

Getting live exchange rates in Power BI is one of those tasks that looks finished long before it actually is. You paste an API URL into Get Data → Web, a rate table appears, your converted revenue measure lights up, and you publish. Two days later the dataset refresh fails in the Power BI Service with a message about a dynamic data source, or it succeeds and quietly converts every historical transaction at today's rate.

This guide covers the whole path: a Power Query M query that returns a clean rate table, the RelativePath pattern that keeps it refreshable in the Service, a historical rates function for converting at the transaction date, a DAX measure for a dynamic reporting currency, and the request-count maths that decides whether your refresh schedule fits inside an API plan.

Every query below is written against the documented Finexly API response shapes. If you have already read our guide to live exchange rates in Excel, the M here will look familiar — but Power BI adds a service-side refresh layer that Excel does not have, and that layer is where most of these projects break.

The Three Ways to Get Rates into Power BI

Method 1: A Live Rate Table with Power Query

Open Transform data → New Source → Blank Query → Advanced Editor and paste this. It returns one row per currency pair with a UTC retrieval timestamp attached.

let
    ApiKey  = "YOUR_API_KEY",
    Base    = "USD",
    Symbols = "EUR,GBP,JPY,CHF,AUD,CAD,SEK,NZD",

    Source = Json.Document(
        Web.Contents(
            "https://api.finexly.com",
            [
                RelativePath = "v1/latest",
                Query        = [ base = Base, symbols = Symbols ],
                Headers      = [ #"Authorization" = "Bearer " & ApiKey ]
            ]
        )
    ),

    Rates    = Record.ToTable( Source[rates] ),
    Renamed  = Table.RenameColumns( Rates, {{"Name", "Quote"}, {"Value", "Rate"}} ),
    AddBase  = Table.AddColumn( Renamed, "Base", each Base, type text ),
    AddStamp = Table.AddColumn( AddBase, "RetrievedUTC", each DateTimeZone.UtcNow(), type datetimezone ),
    Typed    = Table.TransformColumnTypes(
                   AddStamp,
                   {{"Quote", type text}, {"Rate", type number}}
               )
in
    Typed

The endpoint it calls looks like this on the command line, which is worth running once so you can see the shape you are parsing:

curl "https://api.finexly.com/v1/latest?base=USD&symbols=EUR,GBP,JPY" \
  -H "Authorization: Bearer YOUR_API_KEY"

{
  "success": true,
  "base": "USD",
  "date": "2026-09-01",
  "rates": { "EUR": "…", "GBP": "…", "JPY": "…" }
}

Why RelativePath and Query Are Not Optional

This is the single most important line in the article, so it gets its own heading.

If you build the URL as one concatenated string — "https://api.finexly.com/v1/latest?base=" & Base — Power Query cannot determine the destination until the query actually runs. Microsoft classifies that as a dynamic data source, and dynamic data sources are not refreshed in the Power BI Service, for security and privacy reasons. The report will refresh perfectly on your laptop and fail the moment it is scheduled.

Passing the path in RelativePath and the parameters in Query is the documented exception. Power BI can then resolve a single static base URL — https://api.finexly.com — for credential and privacy checks, while the variable parts stay variable. Three rules follow from that:

1. The base URL must be a literal string. No parameters, no concatenation, no & anywhere in it.
2. RelativePath should be the fixed endpoint path. "v1/latest", not "v1/latest?base=USD".
3. Never concatenate inside Query. Pass a record of name/value pairs and let Power Query do the URL encoding. It will also escape characters that would otherwise break the request.

Setting Credentials

The first time you run the query, Power BI asks how to authenticate to https://api.finexly.com. Choose Anonymous. That feels wrong, but it is correct: the API key travels in the Authorization header you supplied in the M code, not through Power BI's credential store. Selecting Web API or Basic here will make Power BI add its own header and the request will be rejected.

Set the privacy level to Public or Organizational consistently across your sources. Mismatched privacy levels are the second most common cause of a refresh that works in Desktop and fails in the Service — Power BI blocks the query rather than risk leaking one source's data into another's request.

One honest caveat: the key is now stored as plain text inside the query. Anyone who opens the .pbix can read it. For anything shared beyond your own machine, promote the key to a Power Query parameter and keep the populated version in a dataflow owned by a service account, so report authors consume the rate table without ever seeing the credential.

Method 2: Historical Rates for Transaction-Date Conversion

A live rate table answers "what is EUR/USD right now". It cannot answer "what was our January revenue in USD", and using it for that is the most expensive mistake in this whole topic — restating last quarter's numbers because the model reconverted them at a new rate is exactly the outcome auditors look for. If your report feeds anything you have to defend later, read our guide on exchange rates and tax reporting alongside this section.

You need a date-keyed rate table. This M function wraps the timeseries endpoint and returns one row per date per currency:

let
    FxHistory = (base as text, symbols as text, startDate as date, endDate as date) as table =>
        let
            ApiKey = "YOUR_API_KEY",

            Source = Json.Document(
                Web.Contents(
                    "https://api.finexly.com",
                    [
                        RelativePath = "v1/timeseries",
                        Query = [
                            base       = base,
                            symbols    = symbols,
                            start_date = Date.ToText( startDate, [Format = "yyyy-MM-dd", Culture = "en-US"] ),
                            end_date   = Date.ToText( endDate,   [Format = "yyyy-MM-dd", Culture = "en-US"] )
                        ],
                        Headers = [ #"Authorization" = "Bearer " & ApiKey ]
                    ]
                )
            ),

            Days      = Table.RenameColumns( Record.ToTable( Source[rates] ), {{"Name", "RateDate"}} ),
            Expanded  = Table.ExpandRecordColumn( Days, "Value", Record.FieldNames( Days{0}[Value] ) ),
            Unpivoted = Table.UnpivotOtherColumns( Expanded, {"RateDate"}, "Quote", "Rate" ),
            AsDate    = Table.TransformColumns(
                            Unpivoted,
                            {{"RateDate", each Date.FromText( _, [Format = "yyyy-MM-dd", Culture = "en-US"] ), type date}}
                        ),
            AddBase   = Table.AddColumn( AsDate, "Base", each base, type text ),
            Typed     = Table.TransformColumnTypes( AddBase, {{"Quote", type text}, {"Rate", type number}} )
        in
            Typed
in
    FxHistory

Two details in there are doing real work:

• Culture = "en-US" on both Date.ToText and Date.FromText. Without it, a machine set to a German or French locale sends 01.09.2026 as start_date and the API rejects it — or worse, a colleague's refresh produces a differently-shaped table than yours. Locale is the invisible variable in every Power Query project that spans more than one country.
• Table.UnpivotOtherColumns. The API returns dates as record keys with a nested record of currencies. Unpivoting to a long RateDate / Base / Quote / Rate shape gives you a table that joins cleanly to a date dimension and does not need reshaping every time you add a currency.

Invoke it once per load: FxHistory( "USD", "EUR,GBP,JPY", #date(2026,1,1), Date.From( DateTime.LocalNow() ) ).

Because rates for a past date never change, this table is the textbook case for incremental refresh: partition on RateDate, refresh the last 7 days, archive everything older. Your refresh time stops growing with your history, and your request count stops growing with it too.

Don't Call the API Once Per Row

The pattern that kills these models is invoking a rate function as a custom column on the fact table. Ten thousand transactions means ten thousand HTTP requests per refresh, a refresh that takes forty minutes, and a quota bill that arrives before lunch.

Do the arithmetic before you build. Power BI Pro allows 8 scheduled refreshes per dataset per day; Premium Per User and Fabric capacity allow 48. A single /v1/latest call covering every currency you need, refreshed at the Pro maximum, costs 8 requests a day — roughly 240 a month, comfortably inside a free tier of 1,000. The same schedule with a per-row function is unbounded. Even at the PPU maximum of 48 refreshes, one consolidated call lands at about 1,440 requests a month, which is a small paid plan rather than a per-row catastrophe. Our pricing plans list the thresholds if you need to size this precisely.

When more than one report needs the rates, move the query into a dataflow. The dataflow calls the API on its own schedule and materialises the result; every downstream dataset reads from storage instead of hitting the API again. Five reports on the same dataflow make one set of requests, not five.

Converting Amounts: Power Query Merge vs DAX Measure

There are two legitimate places to apply the conversion, and they answer different questions.

Option A: Merge in Power Query

If your reporting currency is fixed — everything is reported in USD, full stop — do the join at load time.

1. Load the fact table into Power Query.
2. Merge Queries against the rate table, matching on currency code and date. Hold Ctrl and select the columns in the same order in both tables.
3. Expand only the Rate column and set it to Fixed decimal number.
4. Select Amount and Rate, then Add Column → Standard → Multiply.
5. Disable load on the rate table if nothing else references it.

This is fast, it materialises once, and it cannot be gamed by a slicer. That last point is the trade-off.

Option B: A DAX Measure with a Selectable Reporting Currency

If users need to flip the whole report between USD, EUR and GBP, the conversion has to happen at query time. Add a disconnected Reporting Currency table with one column of ISO codes, put it in a slicer, and write:

Revenue (Reporting Currency) =
VAR ReportingCurrency = SELECTEDVALUE( 'Reporting Currency'[Code], "USD" )
RETURN
SUMX (
    'Sales',
    VAR TxCurrency = 'Sales'[CurrencyCode]
    VAR TxDate     = 'Sales'[OrderDate]
    VAR Rate =
        CALCULATE (
            MAX ( 'FX Rates'[Rate] ),
            REMOVEFILTERS ( 'FX Rates' ),
            'FX Rates'[Base]     = TxCurrency,
            'FX Rates'[Quote]    = ReportingCurrency,
            'FX Rates'[RateDate] = TxDate
        )
    RETURN 'Sales'[Amount] * Rate
)

Three things to notice:

• The VAR lines are load-bearing. Capturing TxCurrency and TxDate into variables before CALCULATE pins them to the current SUMX row. Referencing the columns directly inside the filter arguments invites a context-transition bug that produces plausible-looking but wrong totals.
• REMOVEFILTERS( 'FX Rates' ) stops any incoming filter on the rate table from narrowing the lookup.
• A missing rate returns BLANK(), and Amount * BLANK() is BLANK(). That is the behaviour you want. A row that silently falls back to the unconverted amount is a row that inflates your total by whatever the exchange rate happened to be.

The Relationship Pitfall

Do not create a physical relationship from the fact table to the rate table on currency code alone. Currency code is not unique in a date-keyed rate table, so Power BI proposes a many-to-many relationship, and many-to-many with bidirectional filtering will happily fan out your row count and multiply your revenue. Either use the composite merge in Power Query, or keep the rate table disconnected and look rates up in DAX as above.

Five Mistakes That Produce Wrong Numbers Without an Error

1. Concatenated URLs. Refreshes in Desktop, fails in the Service with "This dataset includes a dynamic data source." Fixed by RelativePath and Query.
2. Inverted pairs. base=USD&symbols=EUR returns USD→EUR. If your fact table stores EUR amounts you need the reciprocal. Sanity-check one known pair by hand before you trust a single total. Our ISO 4217 reference is worth a look if you are unsure which code belongs on which side.
3. Today's rate on historical rows. The number changes every refresh and nobody notices until someone compares two exports of the same report.
4. Locale-formatted dates and decimals. A comma decimal separator turns 1,0842 into text, Table.TransformColumnTypes returns an error the model treats as blank, and the affected rows disappear from your totals.
5. Rounding at the wrong step. Round once, at presentation, after the multiplication. Rounding the rate to four decimals before multiplying a seven-figure amount introduces a visible discrepancy against the accounting system.

Frequently Asked Questions

Can Power BI refresh exchange rates automatically?

Yes. A published dataset can be scheduled for up to 8 refreshes per day on Power BI Pro and 48 per day on Premium Per User or Fabric capacity. Premium's XMLA endpoint lets external tools trigger refreshes outside those limits. The rate query itself needs no special treatment beyond being built with Web.Contents + RelativePath so the Service accepts it.

How do I pass an API key securely in Power BI?

Put it in the Headers record of Web.Contents as Authorization = "Bearer " & ApiKey and choose Anonymous authentication when Power BI prompts. This keeps the key out of the URL, out of proxy logs, and out of browser history. It does not encrypt the key inside the .pbix — for shared models, hold the key in a dataflow owned by a service account and let reports read the materialised table.

Why does my Power BI dataset say "This dataset includes a dynamic data source"?

Because the URL is assembled in code, so Power BI cannot verify the destination before the query runs, and it refuses to refresh it in the Service. Rebuild the call with a static base URL plus RelativePath and Query options, republish, and re-enter the credential for the base URL.

How do I convert amounts using the exchange rate on the transaction date?

Load a date-keyed rate table from a timeseries endpoint, then match on both currency and date — either via a composite merge in Power Query, or with a CALCULATE lookup inside SUMX as shown above. Never join on currency alone; you will silently pick whichever rate row happens to sort first.

Is there a free currency API that works with Power BI?

Yes. Finexly's free currency API tier includes 1,000 requests per month with no credit card, which covers a Pro-licensed dataset refreshing eight times a day with a wide margin. If you are weighing providers on refresh limits, historical depth or currency coverage, compare currency APIs before you wire one into a model you will be maintaining for years.

Get Started with Finexly

Ready to put live exchange rates into your Power BI reports? Get your free Finexly API key — no credit card required. Start with 1,000 free requests per month and upgrade as you grow. Real-time and historical rates for 170+ currencies, from one REST API that behaves the same whether you are calling it from Power Query, Python or a payment service.