How to use Table.Max?

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