none
Making a list of pairs in all possible ways RRS feed

  • 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

    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

    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