Log In
Register
Home
August 26, 2020
Offline
Given that i need to pull exchange rate from another query/table in power query and later make use of it to do currency conversion, it is taking a quite a long time.
Is there a better way to write this power query M code to improve the performance?
I am concerned about these two lines. Can i make use of Table.Buffer or variable to help with performance.
#"Added Custom2" = Table.AddColumn(#"Filtered Rows1", "Rate", each let mydate=[Document Date],
mycur=[Currency]
in
(Table.Max(
Table.SelectRows(#"ExchRate",each [Date] <= mydate and [Cur] = mycur),"Date")) [Rate]),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "USDAmt", each [Value]/[Rate]),
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Francis,
Try Table.Buffer in the previous step.
Also, try loading the #"ExchRate" table in memory before adding the column with Table.Buffer, otherwise SelectRows is an expensive operation, will load #"ExchRate" table at each row in the new column, If it is not in memory.
Should look like:
ExchRate = Table.Buffer(#"ExchRate")
#"Added Custom2" = Table.AddColumn(#"Filtered Rows1", "Rate", each let mydate=[Document Date],
mycur=[Currency]
in
(Table.Max(
Table.SelectRows(ExchRate ,each [Date] <= mydate and [Cur] = mycur),"Date")) [Rate]),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "USDAmt", each [Value]/[Rate]),
1 Guest(s)
