Answered by:
Making a list of pairs in all possible ways
Question

Hello,
I’d like to make a list of pairs in all possible ways but couldn’t find any clue. Can you advise?
(Example) I have four IDs : a, b, c, and d. I’d like to make the following pairs:
(a,b), (a,c), (a,d), (b,a), (b,c), (b,d), (c,a) (c,b), (c,d), (d,a), (d,b), (d,c)
(a,a), (b,b), (c,c), (d,d)  optional
I really appreciate any help.
Thank you,
YS
Wednesday, October 28, 2020 2:59 PM
Answers

Hi
No idea how you want your lists, in a table below:
let Source = #table(type table [ID = text], {{"a"},{"b"},{"c"},{"d"}}), Pairs = Table.AddColumn(Source, "Pairs", (state)=> Text.Combine( List.Transform(List.RemoveMatchingItems(Source[ID],{state[ID]}), each "(" & state[ID] & "," & _ & ")" ), ", " ), type text ), Optional = Table.AddColumn(Pairs, "Optional", each "(" & [ID] & "," & [ID] & ")", type text ) in Optional
 Proposed as answer by Imke FeldmannMVP, Moderator Sunday, November 1, 2020 6:58 AM
 Marked as answer by RiSCience Monday, November 2, 2020 3:59 PM
Wednesday, October 28, 2020 5:10 PM 
another approach (both in text or in list)
let id_lst = {"a".."d"}, col_1 = Table.FromList(id_lst, null, {"col 1"}), col_2 = Table.AddColumn(col_1, "col 2", each id_lst), expand = Table.ExpandListColumn(col_2, "col 2"), full_pair_lst = Table.AddColumn(expand, "pair", each {[col 1],[col 2]}) [[pair]], full_pair_txt = Table.TransformColumns(full_pair_lst, {"pair", each Text.Combine(List.Transform(_, Text.From), ","), type text}), optional_lst = Table.FromList( List.Transform(id_lst, each {_,_} ), Splitter.SplitByNothing(), {"pair"} ), optional_txt = Table.TransformColumns(optional_lst, {"pair", each Text.Combine(List.Transform(_, Text.From), ","), type text}), pair_without_optional_lst = Table.SelectRows(full_pair_lst, each not List.Contains(List.Buffer(optional_lst[pair]),[pair]) ), pair_without_optional_txt = Table.TransformColumns(pair_without_optional_lst, {"pair", each Text.Combine(List.Transform(_, Text.From), ","), type text}) in pair_without_optional_txt
 Proposed as answer by Imke FeldmannMVP, Moderator Sunday, November 1, 2020 6:58 AM
 Marked as answer by RiSCience Monday, November 2, 2020 8:57 AM
 Unmarked as answer by RiSCience Monday, November 2, 2020 10:39 AM
 Marked as answer by RiSCience Monday, November 2, 2020 3:58 PM
Thursday, October 29, 2020 8:09 AM 
other options as text:
let Source = {"a".."d"}, Pairs = Text.Combine( List.Transform(Source, (i)=> Text.Combine( List.Transform(List.RemoveMatchingItems(Source,{i}), (j)=> "(" & i & "," & j & ")"), ", " ) ), ", " ), Optionals = Text.Combine( List.Transform(Source, each "(" & _ & "," & _ & ")"), ", " ) in "Pairs: " & Pairs & "#(lf)Optionals: " & Optionals
let Source = {"a".."d"}, Pairs = Text.Combine( List.Accumulate(Source, {}, (state,current)=> state & List.Transform(List.RemoveMatchingItems(Source,{current}), each "(" & current & "," & _ & ")" ) ),", " ), Optionals = Text.Combine( List.Transform(Source, each "(" & _ & "," & _ & ")"), ", " ) in "Pairs: " & Pairs & "#(lf)Optionals: " & Optionals
let fxPairs = (SourceList as list, n as number, optional initial as nullable list) as list => let calc = if initial is null then List.Transform(List.Difference(SourceList,{SourceList{n}}), each "(" & SourceList{n} & "," & _ & ")") else initial & List.Transform(List.Difference(SourceList,{SourceList{n}}), each "(" & SourceList{n} & "," & _ & ")") in if n = List.Count(Source)1 then calc else @fxPairs(SourceList, n+1, calc), Source = {"a".."d"}, Pairs = Text.Combine(fxPairs(Source,0),", "), Optionals = Text.Combine( List.Transform(Source, each "(" & _ & "," & _ & ")"), ", " ) in "Pairs: " & Pairs & "#(lf)Optionals: " & Optionals
 Proposed as answer by Imke FeldmannMVP, Moderator Sunday, November 1, 2020 6:58 AM
 Edited by Lz._ Sunday, November 1, 2020 7:50 AM typo
 Marked as answer by RiSCience Monday, November 2, 2020 3:59 PM
Thursday, October 29, 2020 1:35 PM
All replies

Hi
No idea how you want your lists, in a table below:
let Source = #table(type table [ID = text], {{"a"},{"b"},{"c"},{"d"}}), Pairs = Table.AddColumn(Source, "Pairs", (state)=> Text.Combine( List.Transform(List.RemoveMatchingItems(Source[ID],{state[ID]}), each "(" & state[ID] & "," & _ & ")" ), ", " ), type text ), Optional = Table.AddColumn(Pairs, "Optional", each "(" & [ID] & "," & [ID] & ")", type text ) in Optional
 Proposed as answer by Imke FeldmannMVP, Moderator Sunday, November 1, 2020 6:58 AM
 Marked as answer by RiSCience Monday, November 2, 2020 3:59 PM
Wednesday, October 28, 2020 5:10 PM 
another approach (both in text or in list)
let id_lst = {"a".."d"}, col_1 = Table.FromList(id_lst, null, {"col 1"}), col_2 = Table.AddColumn(col_1, "col 2", each id_lst), expand = Table.ExpandListColumn(col_2, "col 2"), full_pair_lst = Table.AddColumn(expand, "pair", each {[col 1],[col 2]}) [[pair]], full_pair_txt = Table.TransformColumns(full_pair_lst, {"pair", each Text.Combine(List.Transform(_, Text.From), ","), type text}), optional_lst = Table.FromList( List.Transform(id_lst, each {_,_} ), Splitter.SplitByNothing(), {"pair"} ), optional_txt = Table.TransformColumns(optional_lst, {"pair", each Text.Combine(List.Transform(_, Text.From), ","), type text}), pair_without_optional_lst = Table.SelectRows(full_pair_lst, each not List.Contains(List.Buffer(optional_lst[pair]),[pair]) ), pair_without_optional_txt = Table.TransformColumns(pair_without_optional_lst, {"pair", each Text.Combine(List.Transform(_, Text.From), ","), type text}) in pair_without_optional_txt
 Proposed as answer by Imke FeldmannMVP, Moderator Sunday, November 1, 2020 6:58 AM
 Marked as answer by RiSCience Monday, November 2, 2020 8:57 AM
 Unmarked as answer by RiSCience Monday, November 2, 2020 10:39 AM
 Marked as answer by RiSCience Monday, November 2, 2020 3:58 PM
Thursday, October 29, 2020 8:09 AM 
other options as text:
let Source = {"a".."d"}, Pairs = Text.Combine( List.Transform(Source, (i)=> Text.Combine( List.Transform(List.RemoveMatchingItems(Source,{i}), (j)=> "(" & i & "," & j & ")"), ", " ) ), ", " ), Optionals = Text.Combine( List.Transform(Source, each "(" & _ & "," & _ & ")"), ", " ) in "Pairs: " & Pairs & "#(lf)Optionals: " & Optionals
let Source = {"a".."d"}, Pairs = Text.Combine( List.Accumulate(Source, {}, (state,current)=> state & List.Transform(List.RemoveMatchingItems(Source,{current}), each "(" & current & "," & _ & ")" ) ),", " ), Optionals = Text.Combine( List.Transform(Source, each "(" & _ & "," & _ & ")"), ", " ) in "Pairs: " & Pairs & "#(lf)Optionals: " & Optionals
let fxPairs = (SourceList as list, n as number, optional initial as nullable list) as list => let calc = if initial is null then List.Transform(List.Difference(SourceList,{SourceList{n}}), each "(" & SourceList{n} & "," & _ & ")") else initial & List.Transform(List.Difference(SourceList,{SourceList{n}}), each "(" & SourceList{n} & "," & _ & ")") in if n = List.Count(Source)1 then calc else @fxPairs(SourceList, n+1, calc), Source = {"a".."d"}, Pairs = Text.Combine(fxPairs(Source,0),", "), Optionals = Text.Combine( List.Transform(Source, each "(" & _ & "," & _ & ")"), ", " ) in "Pairs: " & Pairs & "#(lf)Optionals: " & Optionals
 Proposed as answer by Imke FeldmannMVP, Moderator Sunday, November 1, 2020 6:58 AM
 Edited by Lz._ Sunday, November 1, 2020 7:50 AM typo
 Marked as answer by RiSCience Monday, November 2, 2020 3:59 PM
Thursday, October 29, 2020 1:35 PM