Power BI and Excel: Currency APIs Compared for Exchange Rate Data

Reviewed by Madhushan, Fintech Developer — November 2026
Data analytics dashboard on screen

Data analysts and financial modelers live in Power BI and Excel. Whether you are building a multi-currency revenue dashboard, a treasury report, or a procurement cost tracker, you need exchange rate data that flows cleanly into your existing workflows — without requiring Python scripts, custom connectors, or a data engineering team.

The challenge: most currency APIs were designed for software developers, not data analysts. They return nested JSON that breaks Power Query, require OAuth flows that Excel cannot handle, or lock historical data behind pricing tiers that do not fit a department budget, or cap free plans so tightly that a scheduled refresh burns through them in a week.

This article compares the 5 most relevant currency APIs for Power BI and Excel users in 2026. We show working Power Query M code for each so you can see exactly what the connection looks like. Vendor facts below were re-checked on 24 October 2026. We build AllRatesToday, so read that section with that in mind; the short version is that it fits when you need 60-second refresh or official central-bank rates, and a free alternative is often enough when you do not.

Side-by-Side Comparison

Here is how the top 5 currency APIs compare for Power BI and Excel use cases:

API Power BI Compatible Excel Add-in Free Tier Historical Data Update Speed
AllRatesToday Yes (native Web connector) WEBSERVICE formula Free, no CC (non-commercial) Paid plans (from €4.99/mo) Every 60s
Open Exchange Rates Yes No 1,000 req/mo (USD base) Paid only ($12/mo) Hourly
Fixer.io Yes (HTTPS) No 100 req/mo (non-commercial) Yes, on free tier Hourly
ExchangeRate-API Yes No 1,500 req/mo Paid only (Pro $10/mo) Daily
Frankfurter Yes No Keyless, no caps Yes (ECB, back to 1999) Daily (16:00 CET)

Key takeaway: All five connect to Power BI through the standard Web connector; none ships a native Power BI connector. The real differences are refresh frequency, where historical data sits, and the free-tier caps. If daily ECB rates are enough, Frankfurter costs nothing. AllRatesToday stands out on this list for 60-second updates, official central-bank rates, and CSV/XLSX output, but its history is on paid plans from €4.99/mo.

1. AllRatesToday — 60-Second Rates and Central-Bank Data

AllRatesToday returns flat JSON that Power Query parses without much transformation. The API uses Bearer token authentication, which both Power BI Desktop and Excel's Power Query support natively. Table endpoints can also return format=csv, xml or xlsx, which is handy when you would rather use Csv.Document or Excel.Workbook than parse JSON. Historical data is included on every paid plan (from €4.99/mo); the free tier covers latest rates for testing and non-commercial use. There is no native Power BI connector: everything below is Web.Contents in Power Query. Alongside mid-market rates it also serves official rates published by 100+ central banks and tax authorities, which matters for statutory or month-end reporting.

Power Query M — Get Latest Rates

Open Power BI Desktop, click Get Data > Web, choose Advanced, and paste the following Power Query M code into a blank query:

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

This returns a clean two-column table (Currency, Rate) that you can immediately use in relationships, measures, and visuals.

Power Query M — Get Historical Rates for a Date Range

let
    ApiKey = "YOUR_API_KEY",
    Source = Json.Document(
        Web.Contents(
            "https://allratestoday.com/api/v1/rates",
            [
                Query = [
                    source = "USD",
                    target = "EUR",
                    from = "2026-01-01",
                    to = "2026-05-24"
                ],
                Headers = [Authorization = "Bearer " & ApiKey]
            ]
        )
    ),
    RatesList = Source[rates],
    ToTable = Table.FromList(RatesList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ExpandedColumns = Table.ExpandRecordColumn(ToTable, "Column1", {"time", "rate"}, {"Date", "Rate"}),
    TypedColumns = Table.TransformColumnTypes(ExpandedColumns, {{"Date", type date}, {"Rate", type number}})
in
    TypedColumns

This query pulls daily USD/EUR rates for an entire date range into a table you can plot as a line chart. Combine it with your revenue data to calculate localized totals over time.

Power Query M — Convert an Amount

let
    ApiKey = "YOUR_API_KEY",
    Amount = 50000,
    Source = Json.Document(
        Web.Contents(
            "https://allratestoday.com/api/v1/rates",
            [
                Query = [
                    source = "USD",
                    target = "GBP",
                    amount = Number.ToText(Amount)
                ],
                Headers = [Authorization = "Bearer " & ApiKey]
            ]
        )
    ),
    Result = Source[to][amount],
    RateUsed = Source[rate]
in
    [ConvertedAmount = Result, ExchangeRate = RateUsed, OriginalAmount = Amount]

Excel — WEBSERVICE Formula

If you are working in Excel (Microsoft 365 or Excel 2019+), you can pull exchange rates directly into cells without Power Query:

=VALUE(MID(
    WEBSERVICE("https://allratestoday.com/api/v1/rates?source=USD&target=EUR&api_key=YOUR_API_KEY"),
    FIND("""rate"":",
        WEBSERVICE("https://allratestoday.com/api/v1/rates?source=USD&target=EUR&api_key=YOUR_API_KEY")
    ) + 7,
    10
))

For a cleaner approach, use Excel's built-in Data > From Web feature which leverages Power Query under the hood and returns structured data.

Excel — Power Query (Get & Transform)

In Excel, go to Data > Get Data > From Other Sources > From Web, then enter:

https://allratestoday.com/api/v1/rates?source=USD&api_key=YOUR_API_KEY

Excel will open the Power Query Editor. Click Into Table, expand the rates record, and you will get a two-column table of currency codes and exchange rates that refreshes on demand or on a schedule.

Pro tip: In Power BI Service, you can schedule automatic refreshes up to 8 times per day. Combined with AllRatesToday's 60-second update frequency, your dashboards will always show near-real-time exchange rates without manual intervention.

2. Open Exchange Rates

Open Exchange Rates works with Power BI's Web connector but has two limitations that frustrate data analysts. First, the free tier locks you to USD as the base currency, which means you need to calculate cross-rates manually in DAX if your reporting currency is EUR or GBP. Second, historical data requires a paid plan starting at $12/month.

Power Query M

let
    AppId = "YOUR_APP_ID",
    Source = Json.Document(
        Web.Contents(
            "https://openexchangerates.org/api/latest.json",
            [Query = [app_id = AppId, base = "USD"]]
        )
    ),
    Rates = Record.ToTable(Source[rates]),
    RenamedColumns = Table.RenameColumns(Rates, {{"Name", "Currency"}, {"Value", "Rate"}})
in
    RenamedColumns

3. Fixer.io

Fixer's free plan (checked October 2026) supports HTTPS, updates hourly and includes the historical endpoint, so it works with Power BI's Web connector out of the box. The constraints are the 100 requests per month cap, which a scheduled refresh can exhaust quickly, and the non-commercial licence on the free tier. Paid plans start at $14.99/month. Its sibling currencylayer has the same $14.99 entry price with HTTPS and history on the free tier, but daily rather than hourly updates.

Power Query M

let
    ApiKey = "YOUR_API_KEY",
    Source = Json.Document(
        Web.Contents(
            "https://data.fixer.io/api/latest",
            [Query = [access_key = ApiKey, base = "EUR", symbols = "USD,GBP,JPY"]]
        )
    ),
    Rates = Record.ToTable(Source[rates])
in
    Rates

4. ExchangeRate-API

ExchangeRate-API offers a straightforward REST endpoint that works with Power BI. The JSON response is clean and easy to parse. However, rates update only once per day, and historical data requires a paid plan.

Power Query M

let
    ApiKey = "YOUR_API_KEY",
    Source = Json.Document(
        Web.Contents(
            "https://v6.exchangerate-api.com/v6/" & ApiKey & "/latest/USD"
        )
    ),
    Rates = Record.ToTable(Source[conversion_rates]),
    RenamedColumns = Table.RenameColumns(Rates, {{"Name", "Currency"}, {"Value", "Rate"}})
in
    RenamedColumns

5. Frankfurter

Frankfurter is open source and free with no API key required. This makes it the easiest API to set up in Power BI — just paste a URL and go. The catch: it only provides European Central Bank reference rates, which cover about 30 currencies, update once per business day, and do not include weekends or holidays.

Power Query M

let
    Source = Json.Document(
        Web.Contents(
            "https://api.frankfurter.app/latest",
            [Query = [from = "USD", to = "EUR,GBP,JPY,CAD,AUD"]]
        )
    ),
    Rates = Record.ToTable(Source[rates]),
    RenamedColumns = Table.RenameColumns(Rates, {{"Name", "Currency"}, {"Value", "Rate"}})
in
    RenamedColumns

Building a Multi-Currency Dashboard in Power BI

Here is a practical example of how to build a revenue dashboard that converts sales from multiple currencies into a single reporting currency using AllRatesToday.

Step 1: Create the Exchange Rate Query

In Power BI Desktop, open the Power Query Editor and create a new blank query with the AllRatesToday M code shown above. Name this query ExchangeRates.

Step 2: Create a DAX Measure for Converted Revenue

Converted Revenue (USD) =
VAR CurrentCurrency = SELECTEDVALUE(Sales[Currency])
VAR ExchangeRate =
    LOOKUPVALUE(
        ExchangeRates[Rate],
        ExchangeRates[Currency],
        CurrentCurrency
    )
RETURN
    IF(
        CurrentCurrency = "USD",
        SUM(Sales[Amount]),
        DIVIDE(SUM(Sales[Amount]), ExchangeRate, 0)
    )

Step 3: Schedule Automatic Refresh

After publishing to Power BI Service, configure a scheduled refresh under Dataset Settings > Scheduled Refresh. Set it to refresh every 3 hours during business hours to keep your exchange rates current throughout the trading day.

Important: When using Power BI Service scheduled refresh with API calls, you need to configure the data source credentials. Go to Settings > Data source credentials > Edit credentials and set the authentication type to Anonymous (the API key is passed in the query header).

Excel Multi-Currency Pricing Sheet

Data analysts often need a simple spreadsheet that pulls live rates and converts a list of prices. Here is how to set this up in Excel using Power Query.

Step 1: Load Exchange Rates via Power Query

Go to Data > Get Data > From Web and paste your AllRatesToday API URL. Load the resulting table to a worksheet named Rates.

Step 2: Use XLOOKUP for Conversion

=B2 * XLOOKUP(C2, Rates[Currency], Rates[Rate], 0)

Where B2 is your amount, C2 is the target currency code, and the Rates table contains the exchange rates loaded from AllRatesToday.

Step 3: Refresh on Open

Right-click the Rates query in the Queries & Connections pane, select Properties, and check Refresh data when opening the file. Every time you open the workbook, Excel will pull the latest exchange rates automatically.

Where AllRatesToday Fits

Having connected all five to Power BI Desktop and Excel, these are the cases where AllRatesToday is worth choosing over the free options:

Which One Should You Pick?

Quick Reference

Power Your Dashboards with Real-Time Exchange Rates

Get your free API key in 30 seconds and test the connection in Power Query. Connect Power BI or Excel to 160+ mid-market exchange rates and 100+ central-bank feeds — no credit card. Compare all options on our Exchange Rate API page.

Get Your Free API Key

Related Articles