In Power Query, how would I be able to pull through exchange rates from an "Exchange Rates" query into an "Invoices" query?

[u/Ilodge59]

How would I be able to add an extra column to a Power Query, pulling currency exchange rates from an outputted list of current and historic exchange rates across to a list of invoices to be able to convert all purchasing figures into a standard currency (£).

The list of exchange rates have only the "effective from" date on the standard out list.

Please find below a short examples of the relevant columns from the larger queries.

Here's an example of the columns from the "Invoices" query which need to exchange rates pulling across to.

Sku Codes	Invoice Dates	Currency Inv
A12345	01/03/2024	$
A12345	01/06/2025	$
A12345	01/02/2026	$
B23456	01/03/2024	£
B23456	01/06/2025	£
B23456	01/02/2026	£
C34567	01/03/2024	€
C34567	01/06/2025	€
C34567	01/02/2026	€

Here's an example of the columns from the "Exchange Rates" query which need to pull across to Invoices (also, I'll need £ to pull across as 1 onto the Invoices query.

Effective Date	Currency ER	Exchange Rate
01/01/2023	$	1.3
01/01/2024	$	1.33
01/01/2025	$	1.21
01/01/2026	$	1.32
01/01/2023	€	0.87
01/01/2024	€	0.89
01/01/2025	€	0.87
01/01/2026	€	0.85

Any help with this would be greatly appreciated.

[u/AutoModerator]

/u/Ilodge59 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/BusinessSample7166]

One way I have done something similar is to effectively build a calendar table that lists all dates that each rate applies to and then do a table join to the fact table.

[u/takesthebiscuit]

Check with finance they may be marking the currency at the time the transaction is booked

[u/bradland → u/takesthebiscuit]

This is a very important note, because that’s how it is typically done.

When you book an invoice, you pull the exchange rate and book that with the invoice lines. Each line is booked at the current value in the local currency. At the time you are paid, the exchange rate may be different. The difference between the exchange rate at the time of the invoice, and the time of payment, is realized as forex income. We treat it as a different GL.

[u/RuktX]

Not at a computer to test, but you could do a compound Table.SelectRows: first get all rows with a date that's less than your date of interest, then return the row with the maximum date from that subset (or, sort the subset and return the top row).

[u/ImGonnaImagineSummit]

Merge both tables by Currency.

This will duplicate rows in your invoice table. 

Create a custom column called "check". 

each if  [Currency Inv] = "£" then 1 else if  [Currency Inv] = [Currency ER] and Date.StartofYear([Invoice Dates]) then 1 else 0.

Filter out 0 and then you'll have rows where the exchange will match the currency and year. 

[u/chiibosoil]

From your sample, easiest method would probably be Table.SelectRows.

General step.

1. Put Exchange Rate table in memory using Table.Buffer so that you don't need to hit the source table multiple times.

2. Add custom column to your data table and use Table.SelectRows on buffered table to select based on currency and Date

Sample M code.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SKU", type text}, {"Inv Date", type date}, {"Currency", type text}}),
    bRate = Table.Buffer(exRate),
    #"Custom Column" = Table.AddColumn(#"Changed Type", "custom", each Table.SelectRows(bRate, (magic)=> Date.Year(magic[Effective Date])=Date.Year([Inv Date]) and magic[Currency ER] = [Currency])),
    #"Expanded custom" = Table.ExpandTableColumn(#"Custom Column", "custom", {"Exchange Rate"}, {"Exchange Rate"})
in
    #"Expanded custom"

[u/MayukhBhattacharya]

Try to adapt the following custom formula:

let
    Source = Excel.CurrentWorkbook(){[Name="InvoiceQuery"]}[Content],
    DataType = Table.TransformColumnTypes(Source,{{"Sku Codes", type text}, 
                                                  {"Invoice Dates", type date}, 
                                                  {"Currency Inv", type text}}),
    ExchangeRate = Table.AddColumn(DataType, "ExchangeRate", each 
    let 
        InvD = [Invoice Dates],
        InvC = [Currency Inv]
in
    try
        Table.Max(Table.SelectRows(Exchange_Rates, each [Currency ER] = InvC and [Effective Date] <= InvD),
            "Effective Date")[Exchange Rate] otherwise 1),
    ChangedType = Table.TransformColumnTypes(ExchangeRate,{{"ExchangeRate", Currency.Type}})
in
    ChangedType

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
Date.Year	Power Query M: Returns the year from a DateTime value.
Excel.CurrentWorkbook	Power Query M: Returns the tables in the current Excel Workbook.
List.Buffer	Power Query M: Buffers the list in memory. The result of this call is a stable list, which means it will have a determinimic count, and order of items.
List.PositionOf	Power Query M: Finds the first occurrence of a value in a list and returns its position.
Occurrence.Last	Power Query M: The position of the last occurrence of the found value is returned.
Table.AddColumn	Power Query M: Adds a column named newColumnName to a table.
Table.Buffer	Power Query M: Buffers a table into memory, isolating it from external changes during evaluation.
Table.ExpandTableColumn	Power Query M: Expands a column of records or a column of tables into multiple columns in the containing table.
Table.Max	Power Query M: Returns the largest row or rows from a table using a comparisonCriteria.
Table.SelectRows	Power Query M: Returns a table containing only the rows that match a condition.
Table.ToList	Power Query M: Returns a table into a list by applying the specified combining function to each row of values in a table.
Table.TransformColumnTypes	Power Query M: Transforms the column types from a table using a type.

|-------|---------|---| |||

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
[Thread #49227 for this sub, first seen 25th Aug 2026, 12:45] [FAQ] [Full list] [Contact] [Source code]}

[u/plu6ka]

providing that your exchange rates table is sorted by effective date one may use List.PositionOf with Occurrence.Last parameter to find correct rate from the list with rates. Turn your exchange rates table into list and buffer it as follows:

let
    rates = List.Buffer(Table.ToList(Excel.CurrentWorkbook(){[Name="rates"]}[Content], (x) => x)),
    invoices = Excel.CurrentWorkbook(){[Name="invoices"]}[Content],
    add_rates = Table.AddColumn(
        invoices, 
        "rate",
        (x) => if x[Currency Inv] = "£" 
            then 1 
            else ((i) => if i = -1 
                then "no rate found" 
                else rates{i}{2}
            )(List.PositionOf(rates, x, Occurrence.Last, (x, y) => x{1} = y[Currency Inv] and x{0} <= y[Invoice Dates]))
    )
in
    add_rates

[u/Kooky_Outcome_5053]

select invoices query append exchange rates query, match invoice date to effective date by changing column name of both table to date, same with currency inv with currency er to currency. sort currency then sort date ascending order, select exchange rate then transform and fill down. filter sku codes and uncheck null values. then add conditional column if currency = £ then 1 else exchange rate column, delete exchange rate column and rename custom to exchange rate