March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hi,
I have a Sales Table that I'm attempting to merge with a Currency conversion table using Power Query. I'm using Table.Max() to filter the Currency conversion table to the last date prior to the Sales Date as the currency conversion rate. In addition to this, I need to associate the correct conversion rate with the reporting company. I've added a custom column to the sales table and entered this M code to return a record.
Table.Max(
EUR_LC_Conversion, -- This is the currency conversion table (24 rows)
(EurTable) => -- a variable to allow further filtering of the table.
EurTable[Company Code] = [Company Code]
and
EurTable[Date] <= [Invoiced Date],
"Date"
)
Although this works, the issue I'm finding is it is very slow to load to the point of being unworkable. Is the query correctly structured? Is there a way of getting better performance?
Solved! Go to Solution.
Hi @SteveD2 ,
It's probably slow as you're essentially doing scans of the conversion table as part of your criteria. I'd generally say "either merge it or don't" when it comes to Power Query, meaning either do a formal join between the tables and bring in the column you want, or don't do anything in PQ and manage your requirement through relationships and measures.
In your scenario, a formal join will be tricky as PQ doesn't really like conditional joins, so I'd recommend trying the following to speed things up:
1) Do nothing in PQ
Send both tables to the data model and use LOOKUP(), RELATED(), or measures to grab the value you want from the conversion table.
On balance, this is the option that I would go for. DAX is way faster at this type of thing than PQ.
2) Try buffering your conversion table in your existing code
Try changing the start of your code to:
Table.Max(
Table.Buffer(EUR_LC_Conversion),
This should limit the number of times PQ has to scan the original conversion table to check criteria.
You could also look into using List.Max with Table.SelectRows, but you may end up in a similar situation as the Table.Max syntax where your criteria is trying to match between tables:
List.Max(
Table.SelectRows(
EUR_LC_Conversion,
each //table matching criteria
)[Date]
)
Pete
Proud to be a Datanaut!
Hi @SteveD2 ,
It's probably slow as you're essentially doing scans of the conversion table as part of your criteria. I'd generally say "either merge it or don't" when it comes to Power Query, meaning either do a formal join between the tables and bring in the column you want, or don't do anything in PQ and manage your requirement through relationships and measures.
In your scenario, a formal join will be tricky as PQ doesn't really like conditional joins, so I'd recommend trying the following to speed things up:
1) Do nothing in PQ
Send both tables to the data model and use LOOKUP(), RELATED(), or measures to grab the value you want from the conversion table.
On balance, this is the option that I would go for. DAX is way faster at this type of thing than PQ.
2) Try buffering your conversion table in your existing code
Try changing the start of your code to:
Table.Max(
Table.Buffer(EUR_LC_Conversion),
This should limit the number of times PQ has to scan the original conversion table to check criteria.
You could also look into using List.Max with Table.SelectRows, but you may end up in a similar situation as the Table.Max syntax where your criteria is trying to match between tables:
List.Max(
Table.SelectRows(
EUR_LC_Conversion,
each //table matching criteria
)[Date]
)
Pete
Proud to be a Datanaut!
Thank you. Your comments a very much appreciated. I trialled both methods indicated, the latter one is marginally quicker but neither are workable.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
20 | |
13 | |
10 | |
9 | |
7 |
User | Count |
---|---|
39 | |
27 | |
16 | |
15 | |
10 |