cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jeffshieldsdev Continued Contributor
Continued Contributor

Anyone have experience with Table.AddKey() ?

I just learned about this function a few weeks ago, and I’m wondering:

1) should I always be using this before my merges? (Where I’m not already doing Table.Distinct() obviously) One would assume the Power Query engine would do this kind of optimization automatically.

2) can one assume there’s always a performance benefit to adding keys to both sides of the merge? Or if my fact table is super long, should I just AddKey() on my dimension table?

Thanks!
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
ChrisHaas Solution Sage
Solution Sage

Re: Anyone have experience with Table.AddKey() ?

According to Chris Webb's blog post, Table.AddKey() seems to help only on the "lookup side" of a join.

 

If you use Table.Distinct() on a single column of a table, then it will automatically add a key to that column.

 

It appears that the performance benefit only occurs when using Aggregation, not Expanding.

 

This appears to be because Power Query defaults to a full cartesian cross when joining 2 tables.  When you aggregate, you'll see a great performance improvement.

 

Also, if you're joining with the intention of expanding a column...and you're expecting a single value on the join, consider using aggregation.  You could use Min or First to grab that single value, and you should atill get the speed improvement.

 

Let us know what your results are!

 

View solution in original post

3 REPLIES 3
Super User IV
Super User IV

Re: Anyone have experience with Table.AddKey() ?

@ImkeF might have some thoughts.

 

You could experiment. The Tools tab of the Query Editor ribbon has diagnostics now.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Highlighted
ChrisHaas Solution Sage
Solution Sage

Re: Anyone have experience with Table.AddKey() ?

According to Chris Webb's blog post, Table.AddKey() seems to help only on the "lookup side" of a join.

 

If you use Table.Distinct() on a single column of a table, then it will automatically add a key to that column.

 

It appears that the performance benefit only occurs when using Aggregation, not Expanding.

 

This appears to be because Power Query defaults to a full cartesian cross when joining 2 tables.  When you aggregate, you'll see a great performance improvement.

 

Also, if you're joining with the intention of expanding a column...and you're expecting a single value on the join, consider using aggregation.  You could use Min or First to grab that single value, and you should atill get the speed improvement.

 

Let us know what your results are!

 

View solution in original post

Super User III
Super User III

Re: Anyone have experience with Table.AddKey() ?

Kudos to @ChrisHaas  explanation.

 

If you want to aggregate the joined table, then there are some performance aspects to consider: https://www.thebiccountant.com/2019/10/28/performance-tip-for-aggregations-after-joins-in-power-quer... 

 

But if you're joining distinct rows, then using the Key on the table is the only trick that I'm aware of.

In addition to that, make sure to check other factors that impact performance (especially the disabling of background refresh): https://www.thebiccountant.com/2019/10/28/performance-tip-for-aggregations-after-joins-in-power-quer... 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors