How to expand multiple columns to new rows at the same time?

[zaza]

Greetings

I have 2 colums with lists like so:

Each list contains several values, each of which corresponds to a value in the same position within the second list. Like so:

Column 1  |  Column 2

[1,2,3,4,5]  |  [A,B,C,D,E]

If I expand the first column and than the second column I will end up with a value pair for each element:

Column 1  |  Column 2

1                  A

1                  B

1                  C

1                  D

1                  E

2                  A

2                  B

...                 ...

However I want to end end up with this:

Column 1  |  Column 2

1                  A

2                  B

3                  C

4                  D

5                  E

Does anyone has any idea how could I solve this problem?

Thanks

Zaza

[Anonymous · Accepted solution]

@zaza , try this M code:

et
    <PreviousStep> = some table,
    Add_Zipped = Table.AddColumn(<PreviousStep>, "Zipped", each List.Zip({[Column1], [Column2]}), type list),
    Expand_Zipped = Table.ExpandListColumn(Add_Zipped, "Zipped"),
    Extract_Zipped = Table.TransformColumns(Expand_Zipped, {"Zipped", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    Split_Column = Table.SplitColumn(Extract_Zipped, "Zipped", Splitter.SplitTextByEachDelimiter({";"}, QuoteStyle.Csv, false), {"Zipped.1", "Zipped.2"})
in
    Split_Column

List.Zip() will combine the 2 lists into nested lists of {1, A}, {2, B}, {3, C}, etc.

You expand that to get 5 rows, each row will have 1 of these lists.

You extract the values and add a semicolon between values to delimit.

Then split the column by the semicolon delimiter.

Hope this helps!

~ Chris

[zaza]

@Anonymous thank you for the solution, it works perfectly!