Power BI Insights: Power Query merges; Covid-19 dashboard; Synapse index analysis; Splitting reports

Microsoft Power BI pros share their insights about Power Query merges, working with a Covid-19 dashboard, Synapse index analysis and splitting reports.

Boosting the performance of Power Query merges

Chris Webb detailed the process of removing columns from tables with a Power Query merge operation, continuing a recent series of blogs on the topic. When users merge data from two queries, the M code that Power Query generates uses the Table.Nested.Join function, but the Table.Join function offers another avenue. Unlike the more commonly used function, it allows users to specify the algorithm to be used by the Power Query engine for the merge.

He dwelt on the SortMerge algorithm. Performance is often slow for non-foldable data sources because the tables used in the merge are held in memory. Webb demoed a few queries, shared time results and added:

Now let's talk about the limitations of this approach. First of all, if the data in the columns used to join the two tables together is not sorted in ascending order you won’t get an error message, you’ll just get incorrect data returned, so you really need to be sure that the data is indeed sorted. Secondly, if your data is not sorted, then you can sort it in Power Query before the merge – but since sorting itself takes time and sorting for non-foldable data sources is another one of those operations which requires the table to be held in memory, you’re unlikely to get any performance improvement.

Running averages with a Covid-19 dashboard

FREE Membership Required to View Full Content:

Joining MSDynamicsWorld.com gives you free, unlimited access to news, analysis, white papers, case studies, product brochures, and more. You can also receive periodic email newsletters with the latest relevant articles and content updates.
Learn more about us here

Get full access

or login