Finding count of possible combinations

[Yamabushi]

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!

[slorin · Accepted solution]

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
Group

Stéphane 

[Yamabushi]

Yes! Thank you 🙂

[Yamabushi]

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.

[ChielFaber]

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"