Forum Discussion

Yamabushi's avatar
Yamabushi
Helper I
1 year ago
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...
Need Help
  • slorin's avatar
    slorin
    1 year ago

    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 

slorin's avatar
slorin
Super User
1 year ago

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's avatar
Yamabushi
Helper I
1 year ago

Yes! Thank you 🙂