Power QueryExercises

Currency conversion with an exchange-rate table in Power Query

How-to · By Tahir Nas · Examples run in our simulator · Updated

9 min read

To convert currencies in Power Query, keep the exchange rates in their own table, match each transaction to the right rate by currency and date, multiply, and round the result. With monthly rates that match is a two-column merge on currency and month; with daily rates you take the latest rate on or before each transaction date, so weekends and bank holidays still find a rate.

The two tables

The transactions table has a date, a currency code and an amount in that currency. Spend from several ad platforms billed in different currencies is a typical case. The rate table has a currency code, the date or month the rate applies to, and the rate itself. The examples below convert to pounds sterling (GBP), so each rate is "GBP per one unit of the foreign currency". The rates are illustrative values for the examples, not published rates.

Keeping rates in a table rather than in the query means finance can add next month's rates without touching the logic, and the same table can feed every report that needs a conversion. The mapping tables vs nested if guide makes the same argument for any lookup value.

Monthly rates: merge on currency and month

Many finance teams use one rate per currency per month, such as a month-average or month-end rate. The transaction date becomes a month key with Date.StartOfMonth, and a merge on both currency and month brings in the rate.

let
    Rates = #table(
        type table [Currency = text, Month = date, GBPPerUnit = number],
        {
            {"EUR", #date(2026, 9, 1), 0.8612}, {"EUR", #date(2026, 10, 1), 0.8655},
            {"USD", #date(2026, 9, 1), 0.7418}, {"USD", #date(2026, 10, 1), 0.7531}
        }
    ),
    Sales = #table(
        type table [Invoice = text, Date = date, Currency = text, Amount = number],
        {
            {"S-301", #date(2026, 9, 14), "EUR", 1200}, {"S-302", #date(2026, 9, 30), "USD", 850},
            {"S-303", #date(2026, 10, 2), "EUR", 430.5}, {"S-304", #date(2026, 10, 9), "GBP", 700},
            {"S-305", #date(2026, 10, 20), "USD", 2000}
        }
    ),
    WithMonth = Table.AddColumn(Sales, "Month", each Date.StartOfMonth([Date]), type date),
    Joined = Table.NestedJoin(WithMonth, {"Currency", "Month"}, Rates, {"Currency", "Month"}, "R", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Joined, "R", {"GBPPerUnit"}),
    Rate = Table.AddColumn(Expanded, "Rate", each
        if [Currency] = "GBP" then 1
        else if [GBPPerUnit] = null then error "No rate for " & [Currency] & " in " & Date.ToText([Month], [Format = "yyyy-MM"])
        else [GBPPerUnit], type number),
    GBP = Table.AddColumn(Rate, "AmountGBP", each Number.Round([Amount] * [Rate], 2), type number),
    Result = Table.SelectColumns(GBP, {"Invoice", "Date", "Currency", "Amount", "Rate", "AmountGBP"})
in
    Result

Result

InvoiceDateCurrencyAmountRateAmountGBP
S-3012026-09-14EUR12000.86121033.44
S-3022026-09-30USD8500.7418630.53
S-3032026-10-02EUR430.50.8655372.6
S-3042026-10-09GBP7001700
S-3052026-10-20USD20000.75311506.2

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

InvoiceDateCurrencyAmountRateAmountGBP
S-3012026-09-14EUR1,2000.86121,033.44
S-3022026-09-30USD8500.7418630.53
S-3032026-10-02EUR430.50.8655372.6
S-3042026-10-09GBP7001700
S-3052026-10-20USD2,0000.75311,506.2

The September invoices pick up September rates and the October ones October rates. The GBP invoice has no row in the rate table, so the merge leaves its rate null and the Rate step sets it to 1. Any other currency with no rate for its month gets an error in that cell naming the currency and month, rather than a null that would drop out of totals. You could instead add a GBP row with rate 1 for every month; either way, make the reporting currency explicit.

Both key columns go in the same order on both sides of the merge. Table.NestedJoin uses a left outer join when no join kind is given (Table.NestedJoin, Microsoft Learn), which keeps every transaction even when a rate is missing, so the guard in the Rate step can report the gap. Date.StartOfMonth returns "the start of the month" containing a date (Date.StartOfMonth, Microsoft Learn).

The lesson on merging on composite keys explains two-column keys, and the month starts, month ends and period keys lesson covers building the month key.

Daily rates: the latest rate on or before the date

Daily rates have gaps. Many rate sources publish only on working days, so a sale on a Sunday has no exact match. An exact merge would leave that sale without a rate. The usual rule is to use the most recent rate on or before the transaction date.

For each transaction, filter the rate table to that currency and to dates on or before the transaction, then take the row with the latest date. Table.Max returns the largest row by the column you name, and returns its optional default when the table is empty (Table.Max, Microsoft Learn).

let
    Rates = #table(
        type table [Currency = text, Date = date, GBPPerUnit = number],
        {
            {"EUR", #date(2026, 10, 1), 0.8650}, {"EUR", #date(2026, 10, 2), 0.8661}, {"EUR", #date(2026, 10, 5), 0.8644},
            {"USD", #date(2026, 10, 1), 0.7525}, {"USD", #date(2026, 10, 2), 0.7540}, {"USD", #date(2026, 10, 5), 0.7512}
        }
    ),
    Sales = #table(
        type table [Invoice = text, Date = date, Currency = text, Amount = number],
        {
            {"S-303", #date(2026, 10, 2), "EUR", 430.5},
            {"S-306", #date(2026, 10, 4), "USD", 990},
            {"S-307", #date(2026, 10, 5), "EUR", 75}
        }
    ),
    WithRate = Table.AddColumn(Sales, "Rate", (row) =>
        let
            candidates = Table.SelectRows(Rates, each [Currency] = row[Currency] and [Date] <= row[Date]),
            latest = Table.Max(candidates, "Date", null)
        in
            if latest = null
            then error "No rate for " & row[Currency] & " on or before " & Date.ToText(row[Date], [Format = "yyyy-MM-dd"])
            else latest[GBPPerUnit], type number),
    GBP = Table.AddColumn(WithRate, "AmountGBP", each Number.Round([Amount] * [Rate], 2), type number)
in
    GBP

Result

InvoiceDateCurrencyAmountRateAmountGBP
S-3032026-10-02EUR430.50.8661372.86
S-3062026-10-04USD9900.754746.46
S-3072026-10-05EUR750.864464.83

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

InvoiceDateCurrencyAmountRateRate date usedAmountGBP
S-3032026-10-02EUR430.50.86612026-10-02372.86
S-3062026-10-04USD9900.7542026-10-02746.46
S-3072026-10-05EUR750.86442026-10-0564.83

S-306 is dated Sunday 4 October. There is no USD rate for that day, so it takes Friday 2 October's rate. The query itself returns the Rate column; the "Rate date used" column is shown here to make the fallback visible.

The function uses (row) => rather than each because the inner Table.SelectRows has its own each, and both would otherwise use _. Naming the outer row keeps row[Currency] (the transaction) separate from [Currency] (the rate row).

When there is no earlier rate

A transaction dated before the first rate in the table has nothing to fall back on. Returning null would let the amount quietly drop out of totals, so the query raises an error that names the currency and date:

let
    Rates = #table(type table [Currency = text, Date = date, GBPPerUnit = number], {{"EUR", #date(2026, 10, 1), 0.8650}}),
    Sales = #table(type table [Invoice = text, Date = date, Currency = text, Amount = number], {{"S-308", #date(2026, 9, 28), "EUR", 100}}),
    WithRate = Table.AddColumn(Sales, "Rate", (row) =>
        let
            candidates = Table.SelectRows(Rates, each [Currency] = row[Currency] and [Date] <= row[Date]),
            latest = Table.Max(candidates, "Date", null)
        in
            if latest = null
            then error "No rate for " & row[Currency] & " on or before " & Date.ToText(row[Date], [Format = "yyyy-MM-dd"])
            else latest[GBPPerUnit], type number)
in
    WithRate{0}[Rate]

Result: an error

Expression.Error: No rate for EUR on or before 2026-09-28

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

The simulator raises No rate for EUR on or before 2026-09-28. That message tells whoever refreshes the report exactly which rate to add.

Performance with large tables

The query filters the rate table once per transaction, so its cost grows with both tables. If refresh becomes slow, two options help. One is to wrap the rate table in Table.Buffer so it is read into memory once; Microsoft notes this "might or might not" make a query faster and can prevent folding (Table.Buffer, Microsoft Learn), so time the refresh before and after (when Table.Buffer helps and when it hurts explains why). The other, in outline, is to expand the rate table to one row per currency per calendar day, fill each gap with the previous rate using Table.FillDown, and then do an ordinary merge on currency and date. The lesson on fill down shows how Table.FillDown behaves.

Check which way the rate is quoted

A rate of 0.8655 for EUR means 1 euro buys 0.8655 pounds, so you multiply. Some sources quote the other way round: 1 pound buys 1.1554 euros. Then you divide.

let
    UnitsPerGBP = #table(type table [Currency = text, PerGBP = number], {{"EUR", 1.1554}, {"USD", 1.3278}}),
    Converted = Table.AddColumn(UnitsPerGBP, "GBP for 1000", each Number.Round(1000 / [PerGBP], 2), type number)
in
    Converted

Result

CurrencyPerGBPGBP for 1000
EUR1.1554865.5
USD1.3278753.13

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

1,000 euros at 1.1554 euros per pound is 865.5 pounds, and 1,000 dollars at 1.3278 is 753.13 pounds. Name the rate column after its direction (GBPPerUnit, UnitsPerGBP) so nobody has to guess, and test one conversion by hand against the rate source. A rate applied the wrong way round turns 1,000 euros into roughly 1,155 pounds instead of 865, an error that is easy to miss in a large total.

Round with intent

Currency amounts need rounding to the currency's minor unit, which is two decimal places for pounds, euros and dollars. Number.Round breaks ties "by rounding to the nearest even number", which Microsoft notes is "also known as banker's rounding" (Number.Round, Microsoft Learn). Excel users often expect halves to round away from zero (the tiered commission post meets the same choice for payroll): Microsoft's own example shows =ROUND(2.15, 1) returning 2.2 (ROUND function, Microsoft Support).

let
    Half = 1.125,
    Default = Number.Round(Half, 2),
    AwayFromZero = Number.Round(Half, 2, RoundingMode.AwayFromZero)
in
    "Default: " & Number.ToText(Default) & ", away from zero: " & Number.ToText(AwayFromZero)

Result

Default: 1.12, away from zero: 1.13

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

The simulator returns Default: 1.12, away from zero: 1.13. Neither is wrong; they are different policies. Pick the one your finance team uses and pass it explicitly so the next reader can see the choice. The rounding lesson and the banker's rounding glossary entry explain the difference, and the exercise on rounding halves two ways lets you practise both.

Round each converted line, then total the rounded lines, if the converted figures will be posted to a ledger. That way the total equals the sum of what was posted. Converting the total in one go can differ by a penny or two.

Where currency conversion goes wrong

A rate that matches more than once

If the rate table has two rows for the same currency and month (a correction added without removing the old rate), the merge returns both and the transaction is duplicated when expanded. Before the merge, check that currency and month are unique in the rate table, for example by comparing Table.RowCount(Rates) with Table.RowCount(Table.Distinct(Rates, {"Currency", "Month"})). The lesson on predicting row counts after a merge shows what to look for.

Currency codes that don't match

"EUR", "eur" and "EUR " are three different keys to a merge, because M compares text exactly, including case and spaces: in the simulator, "EUR" = "eur" returns false. Trim and upper-case currency codes in both tables before merging, and use the same three-letter codes (EUR, USD, GBP) on both sides so "Euro" in one table never meets "EUR" in the other.

Frequently asked questions

Should I use a month-average or a month-end rate?

That is an accounting policy decision, not a Power Query one, so follow your organisation's policy. The query works the same way with either; only the rate table changes.

Can I convert into several reporting currencies?

Yes. Convert everything into one base currency first, then from the base currency into each reporting currency with a second rate table. That keeps one rate per currency per period and avoids a table of every currency pair.

Where should the rates come from?

From the source your finance team has approved, such as a central bank's published rates or your bank's rates. Load them into a table once per period and keep the table under the same review as other finance inputs. The finance month-end recipes guide shows related month-end patterns. The budget vs actual post uses the same month-key merge. Power Query Exercises has more merge and rounding practice in its exercise library.

Sources

About the author

Tahir Nas, software engineer, writes and reviews the lessons, guides and exercises on Power Query Exercises. Tahir has 8 years of experience in tech, working across system administration, cloud architecture, QA, scripting and automation, and software engineering in many languages and mainstream databases. GitHub profile. Every M example shown with a result was run in the site's simulator when the page was built, and the output is that run's result; examples marked for Excel and Power BI only are shown without output. How content is tested.

Keep reading

Practise Power Query in the browser with graded exercises checked against fresh data.

Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.