When you want 'red,green' and '1,2' to become 'red1,red2,green1,green2' use a cross join!!
me@jaykilleen.com wrote this over 5 years ago and it was last updated over 5 years 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.
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 :)