Data transformation question

[Riaan]

I'd like to combine two tables as mapped out in the example below. I had a go at it in Power Query without success. Any suggestions?

Table A

AA

BB

CC

Table B

1000

2000

3000

These two tables has to be combined to provide the following result:

Column 1       Column 2

AA                1000

AA                2000

AA                3000

BB                1000

BB                2000

BB                3000

CC                1000

CC                2000

CC                3000

[BraneyBI · Accepted solution]

There is a pretty straghtforward join to get this accomplished.  What you are looking to do is create a "Cartesian Join", whereby each row of the first table is paired up with all rows of the section table.

Add a custom column to both tables   Join = 1

Merge the two tables on the Join fields from each

Expand the "NewColumn" field to show the values from the second table.  At this point, you will have 9 rows (3 x 3)

Then just remove the extra "Join" column and you should be good to go!

[Daniil]

DAX way:

Table = GENERATE(Table1, Table2)

Power Query way:

Just add a custom column to Table1 and simply write "Table2" as formula, then expand the column