How to use Table.Max?

[SteveD2]

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?

[BA_Pete · Accepted 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

[SteveD2]

Thank you. Your comments a very much appreciated. I trialled both methods indicated, the latter one is marginally quicker but neither are workable.