Microsoft Power BI Insights: Power Query merge performance; Desktop features; Small multiples

June 4 2020

Power BI pros share their insights on Power Query merge performance, new Power BI Desktop features, small multiples and more.

Boosting performance for Power Query merges

Chris Webb shared a two part article on optimizing the performance of Power Query merges. Known as joining in SQL, merging in Power Query can result in performance issues when refreshing. Using a CSV file and SQL Server Profiler, Webb measured the amount of time it takes for a query to execute, finding it fairly slow with multiple columns. As he ran a series of tests, he found that the size of a table tends to impact performance and wrote:

Why does the number of columns influence the performance of a merge? If you read my recent post on monitoring memory usage with Query Diagnostics, you’ll remember that merges have to take place in memory – so I guess the larger the tables involved in the merge, the more memory is needed and the more paging happens if the 256MB limit is exceeded.

In a second round, Webb tested to see if report performance is impacted by removing unwanted columns from the source table prior to a merge. Reducing the number of columns in a million-row table meant that all the queries exceeded the 256 MB container size limit. He found that removing unwanted columns in the step immediately after a merge brought about the same performance benefits as removing unwanted columns right before.

New features in Power BI Desktop