Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Create a second column with the same all values from the first one by each values of the first

Hello community, I'm trying to create a table with a list of products and by each element of the list create a copy of the list in a second column to count how many times the products are bought together.

 

I got something like this:

 

Product IDProduct
1Coke
2Gum
3Chips
4Water
5Cookies
6Candy

 

I would like to get something like this:

 

Product AProduct B
CokeCoke
CokeGum
CokeChips
CokeWater
CokeCookies
CokeCandy
GumCoke
GumGum
GumChips
GumWater
GumCookies
GumCandy
ChipsCoke
ChipsGum
ChipsChips
ChipsWater
ChipsCookies
ChipsCandy
WaterCoke
WaterGum
WaterChips
WaterWater
WaterCookies
WaterCandy
CookiesCoke
CookiesGum
CookiesChips
CookiesWater
CookiesCookies
CookiesCandy
CandyCoke
CandyGum
CandyChips
CandyWater
CandyCookies
CandyCandy

 

Thanks for reading!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

In Power Query:

Add a custom column that just = 1

Add custom col equal 1.png

 

Merge the table with itself on that column, using full outer.

Merge, Full Outer.png

Expand the new column 

Then remove all the columns you do not want

Set date types

 

Final Table:

Final Table.png

 

View solution in original post

2 REPLIES 2
gooranga1
Power Participant
Power Participant

Hi @Anonymous 

 

  1. Go to Edit Queries
  2. Create a new column in your table in Edit Queries which just equals the number 1.
  3. Create a copy of the table
  4. Merge Queries as New joining on the the custom column of 1. Selct Full Outer (all rows from both) as the Join Kind
  5. Expand Columns and you will get the below.

outer join.PNG

 

pbix linked;

 

pbix

 

 

 

Anonymous
Not applicable

In Power Query:

Add a custom column that just = 1

Add custom col equal 1.png

 

Merge the table with itself on that column, using full outer.

Merge, Full Outer.png

Expand the new column 

Then remove all the columns you do not want

Set date types

 

Final Table:

Final Table.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.