cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Riaan
New Member

Data transformation question

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

 

1 ACCEPTED SOLUTION
BraneyBI
Kudo Commander
Kudo Commander

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.
Join_1.jpg

 

Add a custom column to both tables   Join = 1

 

 

 

 

Join_2.jpg

Merge the two tables on the Join fields from each

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Join_3.jpg

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!

 

 

 

 

 

 

View solution in original post

2 REPLIES 2
BraneyBI
Kudo Commander
Kudo Commander

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.
Join_1.jpg

 

Add a custom column to both tables   Join = 1

 

 

 

 

Join_2.jpg

Merge the two tables on the Join fields from each

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Join_3.jpg

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!

 

 

 

 

 

 

View solution in original post

Daniil
Kudo Kingpin
Kudo Kingpin

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

CustomColumn.PNG

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!