Power BI currency conversion with live and historical rates

Power BI currency conversion works in three steps: load exchange rates into a table with Power Query, attach the right rate to every transaction by date and currency, and divide by it in a DAX measure. Power BI has no exchange rates of its own, so this guide loads them from the currencyapi API: daily rates, monthly averages, or today’s rates, refreshed on a schedule in the Power BI service.

What we verified, as of October 2026: every Power Query (M) query below passes Microsoft’s Power Query parser, every DAX measure passes the DAX Formatter parser, and each function and option was checked against Microsoft’s current Power Query M and DAX reference. The CSV columns come from the API documentation and were checked against a local test server. We didn’t run the queries inside Power BI itself, so test them on your data before you rely on them.

Which rates do you need?

All examples use USD as the reporting currency. For another one, replace "USD" in the queries and in the measure. The Free plan (300 requests a month, daily updates) is for testing and personal projects; a company report needs a paid plan (pricing).

1. Store the API key as a parameter

In Power BI Desktop, open Transform data, then Manage Parameters → New Parameter:

• Name: CurrencyApiKey
• Type: Text
• Current value: your API key from the currencyapi dashboard

The queries below send it in the apikey HTTP header. The first time a query runs, Power BI asks how to connect to https://api.currencyapi.com/: choose Anonymous, since the key is already in the header.

Anyone who can edit the report file can read the parameter. On a paid plan, create a separate API key for the report, so you can revoke it without touching your other apps.

2. Load the latest rates

Home → New Source → Blank Query → Advanced Editor, paste, and name the query LatestRates:

let
    Source = Csv.Document(
        Web.Contents(
            "https://api.currencyapi.com/v3/latest",
            [
                Query = [base_currency = "USD", format = "csv"],
                Headers = [apikey = CurrencyApiKey]
            ]
        ),
        [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]
    ),
    Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
    Typed = Table.TransformColumnTypes(Promoted, {{"value", type number}}, "en-US"),
    Result = Table.RenameColumns(
        Table.SelectColumns(Typed, {"datetime", "currency", "value"}),
        {{"datetime", "UpdatedAt"}, {"currency", "Currency"}, {"value", "Rate"}}
    )
in
    Result

format=csv returns one row per currency (datetime,base_currency,currency,value), which Power Query reads without any JSON expanding. Two details matter:

• The culture "en-US" in Table.TransformColumnTypes. The API writes 0.918456 with a decimal point; on a computer with German or French regional settings, Power Query would otherwise read the point as a thousands separator.
• Rate means units per 1 USD. 0.918456 for EUR means 1 USD = 0.918456 EUR, so an amount in euros divided by the rate gives US dollars.

3. Load daily rates for several years

On the Pro plan and up, /v3/range returns daily rates for up to 366 days per request. This query, DailyRates, requests one calendar year at a time, from FirstYear up to yesterday, and combines them:

let
    FirstYear = 2024,
    Currencies = "EUR,GBP,JPY,CHF",
    Yesterday = Date.AddDays(DateTime.Date(DateTime.LocalNow()), -1),
    LoadYear = (year as number) as table =>
        let
            Start = #date(year, 1, 1),
            End = List.Min({#date(year, 12, 31), Yesterday}),
            Csv = Csv.Document(
                Web.Contents(
                    "https://api.currencyapi.com/v3/range",
                    [
                        Query = [
                            datetime_start = Date.ToText(Start, [Format = "yyyy-MM-dd"]) & "T00:00:00Z",
                            datetime_end = Date.ToText(End, [Format = "yyyy-MM-dd"]) & "T23:59:59Z",
                            accuracy = "day",
                            base_currency = "USD",
                            currencies = Currencies,
                            format = "csv"
                        ],
                        Headers = [apikey = CurrencyApiKey]
                    ]
                ),
                [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]
            )
        in
            Table.PromoteHeaders(Csv, [PromoteAllScalars = true]),
    Combined = Table.Combine(List.Transform({FirstYear .. Date.Year(Yesterday)}, LoadYear)),
    WithDate = Table.AddColumn(
        Combined,
        "Date",
        each Date.FromText(Text.Start([datetime], 10), [Format = "yyyy-MM-dd"]),
        type date
    ),
    Typed = Table.TransformColumnTypes(WithDate, {{"value", type number}}, "en-US"),
    Result = Table.RenameColumns(
        Table.SelectColumns(Typed, {"Date", "currency", "value"}),
        {{"currency", "Currency"}, {"value", "Rate"}}
    )
in
    Result

Each daily row carries the end-of-day rate (2026-01-01T23:59:59Z); Text.Start keeps the date part. List only the currencies your transactions use: smaller tables refresh faster. If your plan allows one currency per range request, the API answers 403 with “You are not allowed to request more than one currency”; then load one currency per query and append them.

The base URL stays a fixed text and only the Query record changes, which is what the Power BI service needs to refresh a web source.

4. Or load monthly average rates (all plans)

/v3/average averages the daily rates per month and returns up to 1,830 days in one request, on every plan. Name this query MonthlyRates:

let
    Source = Csv.Document(
        Web.Contents(
            "https://api.currencyapi.com/v3/average",
            [
                Query = [
                    date_from = "2024-01-01",
                    date_to = Date.ToText(Date.AddDays(DateTime.Date(DateTime.LocalNow()), -1), [Format = "yyyy-MM-dd"]),
                    period = "month",
                    base_currency = "USD",
                    currencies = "EUR,GBP,JPY,CHF",
                    format = "csv"
                ],
                Headers = [apikey = CurrencyApiKey]
            ]
        ),
        [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]
    ),
    Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
    WithMonth = Table.AddColumn(
        Promoted,
        "Month",
        each Date.FromText([period] & "-01", [Format = "yyyy-MM-dd"]),
        type date
    ),
    Typed = Table.TransformColumnTypes(WithMonth, {{"average", type number}}, "en-US"),
    Result = Table.RenameColumns(
        Table.SelectColumns(Typed, {"Month", "currency", "average"}),
        {{"currency", "Currency"}, {"average", "Rate"}}
    )
in
    Result

The CSV has the columns period,date_from,date_to,days,source,base_currency,currency,average,min,max; the query keeps the month and the average. For the European Central Bank’s official averages instead of market rates, add source = "ecb" to the Query record; with source, one request covers at most 366 days, so move date_from to within a year of date_to (official rates).

5. Attach the rate to every transaction

Power BI relationships use one column, but a rate depends on two: the date and the currency. The simplest fix is to merge the rate into the transaction table in Power Query. Assuming a query Sales with the columns Date (date), Currency (text) and Amount, create SalesWithRates:

let
    Source = Sales,
    Merged = Table.NestedJoin(
        Source, {"Date", "Currency"},
        DailyRates, {"Date", "Currency"},
        "Rates", JoinKind.LeftOuter
    ),
    Expanded = Table.ExpandTableColumn(Merged, "Rates", {"Rate"}, {"ApiRate"}),
    WithRate = Table.AddColumn(
        Expanded,
        "Rate",
        each if [Currency] = "USD" then 1 else [ApiRate],
        type number
    ),
    Result = Table.RemoveColumns(WithRate, {"ApiRate"})
in
    Result

USD rows get the rate 1, since the reporting currency isn’t in the rate table. For monthly averages, add a Month column to the sales first (Table.AddColumn(Sales, "Month", each Date.StartOfMonth([Date]), type date)) and merge on {"Month", "Currency"} with MonthlyRates.

The first time you merge a web source with your own data, Power BI may ask for privacy levels for both sources. Set them under File → Options and settings → Data source settings.

6. Convert with DAX

With the rate in every row, the measure is short:

Sales USD =
SUMX (
    SalesWithRates,
    ROUND ( DIVIDE ( SalesWithRates[Amount], SalesWithRates[Rate] ), 2 )
)

ROUND converts each transaction to cents before summing, as an invoice would. DIVIDE returns a blank instead of an error when a rate is missing, and blanks drop out of the sum without a warning, so add a measure that counts them and put it on a card:

Rows without rate =
COUNTROWS ( FILTER ( SalesWithRates, ISBLANK ( SalesWithRates[Rate] ) ) )

Rows without a rate usually mean a currency missing from your query’s currencies list, or transactions outside the loaded date range.

If you’d rather keep Sales and DailyRates as separate tables, LOOKUPVALUE finds the rate by both columns inside the measure (slower on large tables, but no merge needed):

Sales USD (lookup) =
SUMX (
    Sales,
    VAR Rate =
        IF (
            Sales[Currency] = "USD",
            1,
            LOOKUPVALUE (
                DailyRates[Rate],
                DailyRates[Date], Sales[Date],
                DailyRates[Currency], Sales[Currency]
            )
        )
    RETURN
        ROUND ( DIVIDE ( Sales[Amount], Rate ), 2 )
)

And to value open orders at today’s rates, look the rate up in LatestRates by currency alone:

Open orders USD =
SUMX (
    OpenOrders,
    VAR Rate =
        IF (
            OpenOrders[Currency] = "USD",
            1,
            LOOKUPVALUE ( LatestRates[Rate], LatestRates[Currency], OpenOrders[Currency] )
        )
    RETURN
        DIVIDE ( OpenOrders[Amount], Rate )
)

Pick the right data types

• Rates: Decimal number (type number in the queries above). Don’t switch them to Fixed decimal number: that type keeps four digits after the decimal point and cuts off the rest, so a rate of 0.006254 (euros per yen) would become 0.0062, an error of almost 1 %.
• Amounts: Fixed decimal number suits money columns with up to four decimals; Microsoft describes it as the type to use where floating-point rounding errors would otherwise add up.

7. Refresh the rates in the Power BI service

1. Publish the report.
2. In the semantic model’s Settings → Data source credentials, edit https://api.currencyapi.com/ and choose Anonymous with the privacy level you used in Desktop. If saving fails because the service tests the connection without your header, select Skip test connection.
3. Under Refresh, set a schedule. Power BI allows up to 8 scheduled refreshes a day on shared capacity (Pro) and up to 48 on Premium, Premium Per User or Fabric capacity.

Every refresh calls each query again. With 8 refreshes a day for a month:

Refreshing more often than the rates change only spends requests: rates update daily on Free, hourly on Small, every 15 minutes on Pro and every 60 seconds on Medium and up. In DailyRates, every year of history adds one request per refresh, so set FirstYear to the earliest year you actually report on.

Errors you may see

The status codes page explains every error the API returns.

No Power BI?

The same rates work in spreadsheets: see currency data in Excel and currency data in Google Sheets, or look up a past rate with the historical currency converter.