Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
SteveD2
Resolver I
Resolver I

How to use Table.Max?

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?

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

2 REPLIES 2
BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors