Forum Discussion

jesse_james's avatar
jesse_james
Helper I
5 years ago
Solved

Combine to Lists into one table

Hi

is it possible to combine to list into one table in Power Query

currently I have two lists with the same number of objects inside:

 

and I would like to have these two merged 1 to 1 to have two columns

for example 

groupA

and group B has other 4 values in the same row.

I would like to join them to have 4 rows and 2 columns (one column with group A value and second column with groop B)

is it possible?

thank you!

Need Help
  • edhans's avatar
    edhans
    5 years ago

    I think List.Zip might work for you jesse_james - see this:

    So col1 has two lists, 1-4, and 5-8. Col2 has A-D and E-H

    This code turns it into this table:

    let
    Source = 
        #table(
            {"Col1", "Col2"},
                {
                    {{1..4}, {"A".."D"}},
                    {{5..8}, {"E".."H"}}
                }
          ),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each List.Zip({[Col1], [Col2]})),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Col1", "Col2"}),
    #"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"Custom", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Custom.1", "Custom.2"})
in
    #"Split Column by Delimiter"

     

    How to use M code provided in a blank query:
    1) In Power Query, select New Source, then Blank Query
    2) On the Home ribbon, select "Advanced Editor" button
    3) Remove everything you see, then paste the M code I've given you in that box.
    4) Press Done
    5) See this article if you need help using this M code in your model.

1 Reply

  • edhans's avatar
    edhans
    Community Champion
    5 years ago

    I think List.Zip might work for you jesse_james - see this:

    So col1 has two lists, 1-4, and 5-8. Col2 has A-D and E-H

    This code turns it into this table:

    let
    Source = 
        #table(
            {"Col1", "Col2"},
                {
                    {{1..4}, {"A".."D"}},
                    {{5..8}, {"E".."H"}}
                }
          ),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each List.Zip({[Col1], [Col2]})),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Col1", "Col2"}),
    #"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"Custom", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Custom.1", "Custom.2"})
in
    #"Split Column by Delimiter"

     

    How to use M code provided in a blank query:
    1) In Power Query, select New Source, then Blank Query
    2) On the Home ribbon, select "Advanced Editor" button
    3) Remove everything you see, then paste the M code I've given you in that box.
    4) Press Done
    5) See this article if you need help using this M code in your model.