Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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!

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    In Power Query:

    Add a custom column that just = 1

     

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

    Expand the new column 

    Then remove all the columns you do not want

    Set date types

     

    Final Table:

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    In Power Query:

    Add a custom column that just = 1

     

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

    Expand the new column 

    Then remove all the columns you do not want

    Set date types

     

    Final Table:

     

  • gooranga1's avatar
    gooranga1
    Power Participant
    7 years ago

    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.

     

    pbix linked;

     

    pbix