Aggregating Data in Power BI?
Question ID: 109360

I have some order data that I want to combine with every possible combination of products, grouped by order ID. That way I can show the products that most frequently appear together on the same order.
It looks something like this:

OrderID ProductID
1 3
1 7
1 4
2 5
2 8
2 6

and for analysis I want to get to a result that looks like this:

Order ID ProductID1 ProductID2
1 3 7
1 3 4
1 7 4


2 5 6
2 5 8
2 2 6
Marked as spam
Posted by (Questions: 9, Answers: 0)
Asked on February 21, 2020 1:23 pm
Answers (1)
Private answer

The easiest way to do this is to do a full self join between the two tables on the Order ID.  Make sure you  strip both tables down to just OrderID and ProductID first, as this will return every combination of the other rows. This will also include matches with the same element, and flipped matches. IE You'll get rows that look like these

OrderID ProductID1 ProductID2
1 3 3
1 3 7
1 7 3

So the excess that you do not need will need to be trimmed away with formula columns. So make one custom column that is null if ProductID1 = ProductID2, and make another custom column that will concatenate ProductID1 and ProductID2 in order from smallest to largest, then filter that custom column by distinct. Let me know if you need additional help with the syntax of either of those formulas, but that should result in a table that looks exactly like what you are looking for.

Marked as spam
Posted by (Questions: 0, Answers: 7)
Answered on February 26, 2020 2:58 pm

Welcome back to "EyeOnTesting" brought to you by Orasi Software, Inc.

Scroll to Top