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]),
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]),
