Creating All Combinations of Data

[griffo]

Hi there - I'm sure this would have been answered before, but I can't find the right search terms to find my answer.

For the data in the image, I want to create a list of each combination per person of the "classes" where there is a 1 in the cell.

The output would look something like the "Desired Output" image.

Thanks
Aaron

 

[smozgur]

Try the following:

let
    // I used sample binary data. Import the Source with the actual table below
    // Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZU9DoMwDIWvUmVmwCYpcIvuiLEbolKr3r9TK7fNj58TBhSEnM+P59hZFne53h+3/USuc+9nf25bYaHoq1zW7sPmaFQyE8QecsGkoiXZPukJ4FSCHTBp5YSCfW6lV+wX+LEdN4afFOcC+PJrzmw+6CrvqQf/HKwtRc50dQUknw32AtWlIR1mbgHJ9yX9dX1LoQaq0P/fvCV/oHlJI8zH/J9UirDkkj8fW1/uFaIq7hTO7m2gn3Nq0cJG+NkL1zQmvuYDe5N+PT8coX99AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Row Labels" = _t, #"Class 1" = _t, #"Class 2" = _t, #"Class 3" = _t, #"Class 4" = _t, #"Class 5" = _t, #"Class 6" = _t, #"Class 7" = _t, #"Class 8" = _t, #"Class 9" = _t, #"Class 10" = _t, #"Class 11" = _t, #"Class 12" = _t, #"Class 13" = _t]),
    ChangeTypes = Table.TransformColumnTypes(Source,{{"Row Labels", type text}, {"Class 1", Int64.Type}, {"Class 2", Int64.Type}, {"Class 3", Int64.Type}, {"Class 4", Int64.Type}, {"Class 5", Int64.Type}, {"Class 6", Int64.Type}, {"Class 7", Int64.Type}, {"Class 8", Int64.Type}, {"Class 9", Int64.Type}, {"Class 10", Int64.Type}, {"Class 11", type text}, {"Class 12", type text}, {"Class 13", Int64.Type}}),

    UnpivotColumns = Table.UnpivotOtherColumns(ChangeTypes, {"Row Labels"}, "Attribute", "Value"),
    FilterRows = Table.SelectRows(UnpivotColumns, each ([Value] = 1)),
    RemoveColumn = Table.RemoveColumns(FilterRows,{"Value"}),
    GroupRows = Table.Group(RemoveColumn, {"Row Labels"}, {{"Classes", each _, type table [Row Labels=text, Attribute=text]}}),
    IndexClasses = Table.TransformColumns(GroupRows, 
                    {"Classes", (r)  => Table.AddIndexColumn(r, "Index", 1)}
        ),
    GroupClasses = Table.TransformColumns(IndexClasses, 
                    {"Classes", (r)  => Table.AddColumn(r, "Classes", each Table.RemoveRows(r, 0, [Index]))}
        ),
    ExpandClasses = Table.ExpandTableColumn(GroupClasses, "Classes", {"Attribute", "Classes"}, {"Classes.Attribute", "Classes.Classes"}),
    ExpandSubClasses = Table.ExpandTableColumn(ExpandClasses, "Classes.Classes", {"Attribute"}, {"Classes.Classes.Attribute"}),
    RemoveNulls = Table.SelectRows(ExpandSubClasses, each ([Classes.Classes.Attribute] <> null)),
    CombineClasses = Table.AddColumn(RemoveNulls, "Custom", each [Classes.Attribute] & " & " & [Classes.Classes.Attribute]),
    Result = Table.RemoveColumns(CombineClasses,{"Classes.Attribute", "Classes.Classes.Attribute"})
in
    Result

 

[JGordon11]

Here's a way using a recursive function call:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {"Name"}, "Class", "Value"),
    Filter = Table.SelectRows(Unpivot, each ([Value] = 1)),
    RemoveCol = Table.RemoveColumns(Filter,{"Value"}),
    fnPairwise = (lst as list) => let lc = List.Count(lst), ls = List.Skip(lst) in 
                            if lc > 2 then List.Accumulate(ls, {}, (s,c)=> s & {lst{0}  & " & " & c}) & @fnPairwise(ls)
                            else if lc = 2 then {lst{0} & " & " & lst{1}} 
                            else {lst{0}},
    Group = Table.Group(RemoveCol, {"Name"}, {{"Combination", each fnPairwise(_[Class])}}),
    Result = Table.ExpandListColumn(Group, "Combination")
in
    Result

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Class1	Class2	Class3	Class4	Class5	Class6	Class7		Name	Combination
2	Person1	1		1	1		1			Person1	Class1 & Class3
3	Person2			1						Person1	Class1 & Class4
4	Person3	1				1				Person1	Class1 & Class6
5	Person4	1				1	1			Person1	Class3 & Class4
6	Person5	1	1	1	1	1				Person1	Class3 & Class6
7	Person6		1	1	1					Person1	Class4 & Class6
8	Person7	1		1	1			1		Person2	Class3
9	Person8	1	1			1				Person3	Class1 & Class5
10										Person4	Class1 & Class5
11										Person4	Class1 & Class6
12										Person4	Class5 & Class6
13										Person5	Class1 & Class2
14										Person5	Class1 & Class3
15										Person5	Class1 & Class4
16										Person5	Class1 & Class5
17										Person5	Class2 & Class3
18										Person5	Class2 & Class4
19										Person5	Class2 & Class5
20										Person5	Class3 & Class4
21										Person5	Class3 & Class5
22										Person5	Class4 & Class5
23										Person6	Class2 & Class3
24										Person6	Class2 & Class4
25										Person6	Class3 & Class4
26										Person7	Class1 & Class3
27										Person7	Class1 & Class4
28										Person7	Class1 & Class7
29										Person7	Class3 & Class4
30										Person7	Class3 & Class7
31										Person7	Class4 & Class7
32										Person8	Class1 & Class2
33										Person8	Class1 & Class5
34										Person8	Class2 & Class5
35
Sheet1

 

[griffo]

Hi everyone

Thank you - both solutions have worked exactly as expected.

Really appreciate your help, this is for a small sporting association (NFP) and will make the lives of our volunteers significantly easier.