cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
GzRacer Frequent Visitor
Frequent Visitor

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

Accepted Solutions
Super User I
Super User I

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

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
Super User I
Super User I

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

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

gooranga1 Senior Member
Senior Member

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

Hi @GzRacer 

 

  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

 

 

 

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors