CODEWITHSUNDEEP
powerquery
Josías
Josías

Reputation: 77

Powerquery: split text from different columns to same rows

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.

Upvotes: 0

Views: 49

Answers (1)

Aleksei Zhigulin
Aleksei Zhigulin

Reputation: 1634

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

Upvotes: 1

Related Questions