Cross Join in Power Query to Combine Two Tables with Every Combination

When you want 'red,green' and '1,2' to become 'red1,red2,green1,green2' use a cross join!!

me@jaykilleen.com wrote this about 5 years ago and it was last updated about 5 years ago.


← Back to the Posts

I do this sort of thing in scenarios where I might have say:

  • A table of products
  • A table of customers
  • A table of customer discounts by product
  • A table of customer prices by product
  • A table of customer product exclusions

First I can start with the table of products and cross join it with the table of customers to give me one monolithic table with every possible combination of all customers and all products.

I have borrowed this logic from this fantastically better post by Matt Allington at exceloratorbi.

Be careful with the cross join as tables can grow exponentially huge! 1000 customers x 10000 materials... you know the drill. Welcome to a RAM and CPU nightmare. Then go blame Power Query for hogging all your resources.

... fast forward an hour... so I have tried this (I tend to write my blog posts as I am developing an actual report)... it is very slow. So I tried Chris Webb's suggestion of adding a column to each table (say called it cross_join_id with the value "1" (as a number not a string) and then performing a Table.Join on that new field between each table.

It was instant! compared with around 1 minute the other way (adding the table as a Custom column).

Mega confusing post but this is really for my own benefit when I forget and need to trigger my memory :)

Similar