Menu
At the moment, I cannot use a typical database so am using excel temporarily. Any ideas?
The 
739
The CROSS JOIN is used to generate a paired combination of each row of the first table with each row of the second table. This join type is also known as cartesian join.
The CARTESIAN JOIN or CROSS JOIN returns the Cartesian product of the sets of records from two or more joined tables. Thus, it equates to an inner join where the join-condition always evaluates to either True or where the join-condition is absent from the statement.
In Power Query you can apply a Cartesian product on any two tables by adding to the first table a custom column with a reference to the second table. Then, by expanding the new column, you will reach the Cartesian product.
You have 3 dimensions here: dim1 (ABC), dim2 (123), dim3 (XYZ).
Here is how you make a cartesian product of 2 dimensions using standard Excel and no VBA:
1) Plot dim1 vertically and dim2 horizontally. Concatenate dimension members on the intersections:
2) Unpivoting data. Launch pivot table wizard using ALT-D-P (don't hold ALT, press it once). Pick "Multiple consolidation ranges" --> create a single page.. --> Select all cells (including headers!) and add it to the list, press next.
3) Plot the resulting values vertically and disassemble the concatenated strings
Voila, you've got the cross join. If you need another dimension added, repeat this algorithm again.
Cheers,
Constantine.
144
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
