Power BI Insights: Cross joins; Storing data; Dyslexia-friendly formatting; Switch measures

Power BI pros share their tips on cross joins in Power Query, automating longer-term data storage, making reports more usable by people with dyslexia and applying switch measures.

Cross joins in Power Query

Chris Webb explored how to optimize performance with rare cross-join merges. By default, cross joins aren't available as a join kind in Table.Join or Table.NestedJoin M functions, but there are two different ways to create them in Power Query. The first option involves creating a custom column in the query that contains the query targeted for cross-joining. Alternatively, users can create a custom column with a single value and merge the two queries.

To test the two options, Webb created a query called SourceQuery, returning a table with a thousand integers. Through a variety of steps, he created a second column referencing the first query and shared M code to accompany it. Applying timings from Profiler, he tested the performance. Webb found that the second approach, with a custom column containing a single value was more effective and wrote:

There’s another advantage of this second approach too, if you’re using a foldable data source like SQL Server: it is possible to make query folding happen, which is of course incredibly important from a performance point of view, although you have to be careful not to change the data type of your custom column (or at least be careful how you do it).

Setting data to store for two years with Power Query

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