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

June 14 2020

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

About Eamon McCarthy Earls

As the assistant editor at MSDynamicsWorld.com and MSCloudNews.com, Eamon helps to oversee editorial content on the site and supports site management and strategy. He can be reached at eearls@msdynamicsworld.com.

Before joining MSDynamicsWorld.com, Eamon was editor for SearchNetworking.com at TechTarget, where he covered networking technology, IoT, and cybersecurity. He is also the author of multiple books and previously contributed to publications such as the Boston Globe, Milford Daily News, and DefenceWeb.

More about Eamon McCarthy Earls