Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 ID | Product |
1 | Coke |
2 | Gum |
3 | Chips |
4 | Water |
5 | Cookies |
6 | Candy |
I would like to get something like this:
Product A | Product B |
Coke | Coke |
Coke | Gum |
Coke | Chips |
Coke | Water |
Coke | Cookies |
Coke | Candy |
Gum | Coke |
Gum | Gum |
Gum | Chips |
Gum | Water |
Gum | Cookies |
Gum | Candy |
Chips | Coke |
Chips | Gum |
Chips | Chips |
Chips | Water |
Chips | Cookies |
Chips | Candy |
Water | Coke |
Water | Gum |
Water | Chips |
Water | Water |
Water | Cookies |
Water | Candy |
Cookies | Coke |
Cookies | Gum |
Cookies | Chips |
Cookies | Water |
Cookies | Cookies |
Cookies | Candy |
Candy | Coke |
Candy | Gum |
Candy | Chips |
Candy | Water |
Candy | Cookies |
Candy | Candy |
Thanks for reading!
Solved! Go to Solution.
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:
Hi @Anonymous
pbix linked;
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:
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |