Powerquery: split text from different columns to same rows

Question

I have:

column1 | column2 | colum3
a;b;c   | x;y;z   | door;house;tree

Desired result using Excel powerquery:

 a      | x       | door
 b      | y       | house
 c      | z       | tree

I tried with: Text.Split([column1],";") and expand to new lines, obtaining:

a
b
c

However when tried the same with other values, new lines are created instead to use the existent ones.

Aleksei Zhigulin · Accepted Answer · 2020-02-12 09:48:13Z

1

You may use this code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
    rec = Table.ReplaceValue(Source,0,0,(a,b,c)=>Text.Split(a,";"),{"column1", "column2", "column3"}){0},
    table = #table(Record.FieldNames(rec),List.Zip(Record.FieldValues(rec)))
in
    table

answered Feb 12, 2020 at 9:48

Aleksei Zhigulin

1,6241 gold badge10 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Josías Over a year ago

It only works with the first line. But in any case thanks your example I learned the key for the trick is to use "List.Zip". Using it, I solved the problem. Thank you!

Collectives™ on Stack Overflow

Powerquery: split text from different columns to same rows

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related