Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Solved! Go to 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!
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!
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-query-and-power-bi/
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-query-and-power-bi/
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
@ImkeF might have some thoughts.
You could experiment. The Tools tab of the Query Editor ribbon has diagnostics now.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.