Forum Discussion
Finding count of possible combinations
Hi,
I have a table that looks something like this:
| Person | Criteria 1 | Criteria 2 | Criteria 3 |
| A | 1 | 0 | 1 |
| B | 1 | 1 | 0 |
| C | 0 | 1 | 0 |
| D | 0 | 1 | 0 |
| E | 1 | 1 | 0 |
| F | 0 | 0 | 1 |
I need to create a transformation that will count the number of times a person has a combination of different criteria:
| Category 1 | Category 2 | Count |
| Criteria 1 | Criteria 1 | 3 |
| Criteria 1 | Criteria 2 | 2 |
| Criteria 1 | Criteria 3 | 1 |
| Criteria 2 | Criteria 1 | 2 |
| Criteria 2 | Criteria 2 | 4 |
| Criteria 2 | Criteria 3 | 0 |
| Criteria 3 | Criteria 1 | 1 |
| Criteria 3 | Criteria 2 | 0 |
| Criteria 3 | Criteria 3 | 2 |
Any advice would be helpful.
Thank you!
Hi Yamabushi
let
Source = YourSource,
Unpivot = Table.UnpivotOtherColumns(Source, {"Person"}, "Criteria1", "Value"),
Join = Table.NestedJoin(Unpivot, {"Person"}, Unpivot, {"Person"}, "Unpivot", JoinKind.LeftOuter),
Expand = Table.ExpandTableColumn(Join, "Unpivot", {"Criteria1", "Value"}, {"Criteria2", "Value2"}),
Product = Table.AddColumn(Expand, "Product", each [Value] * [Value2], type number),
Group = Table.Group(Product, {"Criteria1", "Criteria2"}, {{"Count", each List.Sum([Product]), Int64.Type}})
in
GroupStéphane
4 Replies
Hi Yamabushi
let
Source = YourSource,
Unpivot = Table.UnpivotOtherColumns(Source, {"Person"}, "Criteria1", "Value"),
Join = Table.NestedJoin(Unpivot, {"Person"}, Unpivot, {"Person"}, "Unpivot", JoinKind.LeftOuter),
Expand = Table.ExpandTableColumn(Join, "Unpivot", {"Criteria1", "Value"}, {"Criteria2", "Value2"}),
Product = Table.AddColumn(Expand, "Product", each [Value] * [Value2], type number),
Group = Table.Group(Product, {"Criteria1", "Criteria2"}, {{"Count", each List.Sum([Product]), Int64.Type}})
in
GroupStéphane
Yes! Thank you 🙂
If I'm interpreting your question the right way you can just use the group by function in the UI.
let
Source = PUTYOURSOURCEHERE,
#"Grouped Rows" = Table.Group(Source, {"Criteria 1", "Criteria 2", "Criteria 3"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
#"Grouped Rows"Hi,
Thanks for your answer, but sadly this is not the solution I'm looking for.
I am looking for a way to transform the first table into the structure seen in the second table. It has all the combinations of Criteria listed in 2 separate columns while the third column shows how many times a person was positive in both criteria of a separate row.
