Anyone have experience with Table.AddKey() ?

[jeffshieldsdev]

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!

[Anonymous · Accepted solution]

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!

[ImkeF]

Kudos to @Anonymous  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... 

[Greg_Deckler]

@ImkeF might have some thoughts.

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