How to Get Live Exchange Rates in Power BI Using an API

If you work with multinational financial data in Power BI, you have almost certainly run into the problem of stale exchange rates. Hardcoded conversion tables go out of date the moment you paste them in. Manual CSV imports break the moment someone forgets to update the file. The clean solution is to connect Power BI directly to a Power BI exchange rate API so that every report refresh pulls the latest rates automatically.

This tutorial walks you through the entire process: connecting to the Exchange Rate API from Power Query, transforming the JSON response into a usable table, building a currency conversion dashboard, and scheduling automatic refreshes in the Power BI Service.

Why You Need a Live Exchange Rate API in Power BI

Most organizations that operate across borders deal with revenue, expenses, or pricing in multiple currencies. Without live rates, your dashboards show numbers that could be hours or days out of date. A Power BI exchange rate API connection solves this by:

• Pulling real-time or daily rates for 160+ currencies on every refresh
• Eliminating manual data entry and the errors that come with it
• Supporting historical lookups so you can restate past figures at the correct rate
• Keeping your currency dimension table always current

The Exchange Rate API provides a REST endpoint that returns JSON, which Power Query handles natively. The free tier gives you 1,500 requests per month, which is more than enough for daily or even hourly dashboard refreshes.

Prerequisites

Before you start, make sure you have:

1. Power BI Desktop installed (any recent version)
2. An API key from exchange-rateapi.com (free signup, no credit card required)
3. Basic familiarity with Power Query (the "Transform Data" editor in Power BI)

Step 1: Fetch Latest Exchange Rates with Power Query M

Open Power BI Desktop and click Transform Data to open the Power Query Editor. Then click New Source > Blank Query. Open the Advanced Editor and paste the following M code:

    let
        ApiKey = "YOUR_API_KEY",
        BaseCurrency = "USD",
        Url = "https://api.allratestoday.com/v1/latest?base=" & BaseCurrency,
        Headers = [
            Authorization = "Bearer " & ApiKey
        ],
        Source = Json.Document(
            Web.Contents(Url, [Headers = Headers])
        ),
        Rates = Record.ToTable(Source[rates]),
        RenamedColumns = Table.RenameColumns(Rates, {
            {"Name", "Currency"},
            {"Value", "Rate"}
        }),
        TypedColumns = Table.TransformColumnTypes(RenamedColumns, {
            {"Currency", type text},
            {"Rate", type number}
        }),
        AddedBase = Table.AddColumn(TypedColumns, "BaseCurrency", each BaseCurrency, type text)
    in
        AddedBase
    

Click Done, then Close & Apply. You now have a table with every supported currency and its exchange rate against USD.

What This Code Does

• Web.Contents sends a GET request to the API with your Bearer token in the Authorization header.
• Json.Document parses the JSON response.
• Record.ToTable converts the rates object (which has currency codes as keys) into a two-column table.
• The remaining steps rename columns and set data types so Power BI treats rates as numbers.

Step 2: Fetch Historical Rates for Trend Analysis

To build trend charts, you need historical data. The Exchange Rate API supports a timeseries endpoint that returns rates for a date range. Add another blank query with this M code:

    let
        ApiKey = "YOUR_API_KEY",
        BaseCurrency = "USD",
        StartDate = "2026-01-01",
        EndDate = "2026-05-21",
        Url = "https://api.allratestoday.com/v1/timeseries?start=" & StartDate
            & "&end=" & EndDate
            & "&base=" & BaseCurrency,
        Headers = [
            Authorization = "Bearer " & ApiKey
        ],
        Source = Json.Document(
            Web.Contents(Url, [Headers = Headers])
        ),
        RatesRecord = Source[rates],
        DateList = Record.FieldNames(RatesRecord),
        DateTable = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}),
        ExpandedRates = Table.AddColumn(DateTable, "Rates", each
            Record.ToTable(Record.Field(RatesRecord, [Date]))
        ),
        Expanded = Table.ExpandTableColumn(ExpandedRates, "Rates", {"Name", "Value"}, {"Currency", "Rate"}),
        Typed = Table.TransformColumnTypes(Expanded, {
            {"Date", type date},
            {"Currency", type text},
            {"Rate", type number}
        })
    in
        Typed
    

This gives you a flat table with columns for Date, Currency, and Rate, which is perfect for line charts showing how EUR, GBP, or JPY have moved over time.

Step 3: Build a Currency Conversion Measure

With the rates table loaded, create a DAX measure that converts any amount from one currency to another:

    ConvertedAmount =
    VAR FromRate =
        LOOKUPVALUE(
            ExchangeRates[Rate],
            ExchangeRates[Currency], SELECTEDVALUE(Transactions[Currency])
        )
    VAR ToRate =
        LOOKUPVALUE(
            ExchangeRates[Rate],
            ExchangeRates[Currency], "EUR"
        )
    RETURN
        SUM(Transactions[Amount]) / FromRate * ToRate
    

This measure dynamically converts transaction amounts to EUR (or any target currency you choose). Because the rates table refreshes from the API, your conversions always use current data.

Step 4: Build the Currency Dashboard

Now create a report page with these visuals:

1. Card visual showing today's USD/EUR rate pulled from the ExchangeRates table
2. Line chart using the historical query, with Date on the X-axis and Rate on the Y-axis, filtered to EUR, GBP, and JPY
3. Matrix visual showing ExchangeRates[Currency] on rows and ExchangeRates[Rate] on values, so stakeholders can see all rates at a glance
4. Slicer for BaseCurrency if you parameterize the base (see the next section)

Making the Base Currency Dynamic

You can let users pick the base currency with a parameter. In Power Query, go to Manage Parameters > New Parameter, name it BaseCurrency, set the type to Text, and default it to USD. Then replace the hardcoded "USD" in your queries with the parameter reference:

    BaseCurrency = BaseCurrencyParam,
    

Add a slicer bound to this parameter on your report page, and users can switch the base currency on the fly.

Step 5: Schedule Automatic Refresh

Once you publish the report to the Power BI Service:

1. Go to the dataset settings
2. Under Data source credentials, click Edit credentials and set the authentication method to Anonymous (the API key is embedded in the query header, so no OAuth is needed)
3. Set the Privacy Level to Organizational or Public
4. Under Scheduled refresh, toggle it on and set the frequency (e.g., once per day at 6 AM)

The Power BI Service will call the Power BI exchange rate API endpoint on your schedule and update every visual that depends on the rates table.

Rate Limit Considerations

The free tier of Exchange Rate API provides 1,500 requests per month. A daily refresh of two queries (latest + timeseries) uses about 60 requests per month, well within the limit. If you have multiple reports or need hourly refreshes, the paid tiers offer higher limits.

Step 6: Add Currency Conversion to an Existing Data Model

In most real-world scenarios, you already have a fact table (e.g., Sales) with an amount column and a currency code column. To integrate the exchange rates:

1. Create a relationship between Sales[CurrencyCode] and ExchangeRates[Currency]
2. Use the ConvertedAmount DAX measure from Step 3 in your existing visuals
3. Every time the dataset refreshes, the rates update and all converted amounts recalculate automatically

This approach keeps your data model clean. The exchange rates table acts as a dimension, and the API is the source of truth.

Error Handling in Power Query

API calls can fail due to network issues or expired keys. Wrap your Web.Contents call in a try...otherwise block to handle errors gracefully:

    let
        ApiKey = "YOUR_API_KEY",
        Url = "https://api.allratestoday.com/v1/latest?base=USD",
        Headers = [Authorization = "Bearer " & ApiKey],
        RawResult = try Json.Document(Web.Contents(Url, [Headers = Headers]))
                    otherwise null,
        Result = if RawResult = null
                 then #table({"Currency", "Rate"}, {})
                 else Record.ToTable(RawResult[rates])
    in
        Result
    

This returns an empty table instead of crashing the entire refresh if the API is temporarily unreachable.

Performance Tips

• Cache results: Power BI caches query results during a single refresh cycle, so referencing the same query multiple times does not trigger multiple API calls.
• Limit currencies: If you only need 10 currencies, add a Table.SelectRows step to filter out the rest. This reduces the size of your model.
• Use incremental refresh for timeseries: For large historical ranges, only fetch new dates on each refresh rather than the entire range.

Wrapping Up

Connecting a Power BI exchange rate API to your reports takes about 15 minutes and eliminates an entire class of data-quality problems. You get live rates on every refresh, historical trend data for analysis, and dynamic currency conversion that works across your entire data model.

The Exchange Rate API returns clean JSON that Power Query parses natively, supports 160+ currencies, and offers a generous free tier. Whether you are building a finance dashboard, a multi-currency sales report, or an FX monitoring tool, this integration gives you reliable, up-to-date data without manual effort.

Ready to add live exchange rates to your Power BI reports? Sign up for a free API key at exchange-rateapi.com and start building your currency dashboard today.