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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

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

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors