Get all combinations result of the two columns.

[KevinChang]

Hello, 

I have a table with below columns, 

Id Name
1 A
2 B
3 C

4 D

what I want to get is a combination result (without duplicate data) to a new table, and I would define the creation rule. 

Like only have combination of 2 numbers or 3 numbers, Can anyone share me how to do it? Really appreciate your helps. 

Id Name
1   A
2   B
3   C
1,2   A,B
1,3   A,C
2,3   B,C
2,4   B,D
3,4   C,D
1,2,3 A,B,C

....

1,2,3,4  A,B,C,D

....

[Anonymous · Accepted solution]

Hi @KevinChang ,

A little modification on Pat's code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJUitWJVjICspzALGMgyxnMMgGyXJRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Number = _t, Letter = _t]),
    Numbers = Source[[Number]],
    Add1 = Table.AddColumn(Numbers, "Custom", each Numbers),
    Expand1 = Table.ExpandTableColumn(Add1, "Custom", {"Number"}, {"Number.1"}),
    Add2 = Table.AddColumn(Expand1, "Custom1", each Numbers),
    Expand2 = Table.ExpandTableColumn(Add2, "Custom1", {"Number"}, {"Number.2"}),
    Add3 = Table.AddColumn(Expand2, "Custom2", each Numbers),
    Expand3 = Table.ExpandTableColumn(Add3, "Custom2", {"Number"}, {"Number.3"}),
    AddDistinctList = Table.AddColumn(Expand3, "DistinctList", each List.Sort(List.Distinct(Record.ToList(_)))),
    #"Removed Duplicates1" = Table.Distinct(AddDistinctList, {"DistinctList"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Removed Duplicates1",{"DistinctList"}),
    AddNumberList = Table.AddColumn(#"Removed Other Columns", "Id", each Text.Combine([DistinctList], ",")),
    AddLetterList = Table.AddColumn(AddNumberList, "Custom", each Text.Combine(List.Transform([DistinctList], each Source{Number.From(_)-1}[Letter]), ",")),
    #"Added Custom" = Table.AddColumn(AddLetterList, "Name", each AddLetterList[Custom]),
    #"Removed Other Columns1" = Table.SelectColumns(#"Added Custom",{"Id", "Name"}),
    #"Expanded Name" = Table.ExpandListColumn(#"Removed Other Columns1", "Name")
in
    #"Expanded Name"

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[wdx223_Daniel · Accepted solution]

recursion function

= let fx=(lst)=>let a=@fx(List.Skip(lst)) in if List.Count(lst)=1 then lst else {lst{0}}&List.Transform(a,each lst{0}&","&_)&a in fx(Source[Id Name])

[Anonymous · Accepted solution]

Hi @KevinChang ,

A little modification on Pat's code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJUitWJVjICspzALGMgyxnMMgGyXJRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Number = _t, Letter = _t]),
    Numbers = Source[[Number]],
    Add1 = Table.AddColumn(Numbers, "Custom", each Numbers),
    Expand1 = Table.ExpandTableColumn(Add1, "Custom", {"Number"}, {"Number.1"}),
    Add2 = Table.AddColumn(Expand1, "Custom1", each Numbers),
    Expand2 = Table.ExpandTableColumn(Add2, "Custom1", {"Number"}, {"Number.2"}),
    Add3 = Table.AddColumn(Expand2, "Custom2", each Numbers),
    Expand3 = Table.ExpandTableColumn(Add3, "Custom2", {"Number"}, {"Number.3"}),
    AddDistinctList = Table.AddColumn(Expand3, "DistinctList", each List.Sort(List.Distinct(Record.ToList(_)))),
    #"Removed Duplicates1" = Table.Distinct(AddDistinctList, {"DistinctList"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Removed Duplicates1",{"DistinctList"}),
    AddNumberList = Table.AddColumn(#"Removed Other Columns", "Id", each Text.Combine([DistinctList], ",")),
    AddLetterList = Table.AddColumn(AddNumberList, "Custom", each Text.Combine(List.Transform([DistinctList], each Source{Number.From(_)-1}[Letter]), ",")),
    #"Added Custom" = Table.AddColumn(AddLetterList, "Name", each AddLetterList[Custom]),
    #"Removed Other Columns1" = Table.SelectColumns(#"Added Custom",{"Id", "Name"}),
    #"Expanded Name" = Table.ExpandListColumn(#"Removed Other Columns1", "Name")
in
    #"Expanded Name"

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[ppm1]

Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJUitWJVjICspzALGMgyxnMMgGyXJRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Number = _t, Letter = _t]),
    Numbers = Source[[Number]],
    Add1 = Table.AddColumn(Numbers, "Custom", each Numbers),
    Expand1 = Table.ExpandTableColumn(Add1, "Custom", {"Number"}, {"Number.1"}),
    Add2 = Table.AddColumn(Expand1, "Custom1", each Numbers),
    Expand2 = Table.ExpandTableColumn(Add2, "Custom1", {"Number"}, {"Number.2"}),
    Add3 = Table.AddColumn(Expand2, "Custom2", each Numbers),
    Expand3 = Table.ExpandTableColumn(Add3, "Custom2", {"Number"}, {"Number.3"}),
    AddDistinctList = Table.AddColumn(Expand3, "DistinctList", each List.Sort(List.Distinct(Record.ToList(_)))),
    #"Removed Duplicates1" = Table.Distinct(AddDistinctList, {"DistinctList"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Removed Duplicates1",{"DistinctList"}),
    AddNumberList = Table.AddColumn(#"Removed Other Columns", "NumberList", each Text.Combine([DistinctList], ",")),
    AddLetterList = Table.AddColumn(AddNumberList, "Custom", each Text.Combine(List.Transform([DistinctList], each Source{Number.From(_)-1}[Letter]), ",")),
    #"Removed Other Columns1" = Table.SelectColumns(AddLetterList,{"NumberList", "Custom"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns1",{{"NumberList", type text}, {"Custom", type text}})
in
    #"Changed Type"

Pat