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

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

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