When you want 'red,green' and '1,2' to become 'red1,red2,green1,green2' use a cross join!!
firstname.lastname@example.org wrote this over 1 year ago and it was last updated over 1 year ago.
I do this sort of thing in scenarios where I might have say:
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.
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 :)