Forum Discussion
Solved
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...
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
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.