cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
luxpbi Member
Member

Split and Transpose in Power Query

Hi all, 

 

I have a table that have columns like this:

Line No_Row No_DescriptionTotaling
400001.A.I.11. Desarrollo200|201|2801|2901|2800

 

What I need is transform it to look like this:

Line No_40000
Row No_1.A.I.1
Description1. Desarrollo
Account200
Account201
Account2801
Account2901
Account2800


I know that I have to split the column by delimiter but I don't know how to create the rows afet. I have tried to transpose but it doen't work. 

Is this possible with M and Power Query? 

 

Thank you a lot in advance for your help! 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Split and Transpose in Power Query

after the split instead of transposing use Unpivot, that should solve it

2 REPLIES 2
Super User
Super User

Re: Split and Transpose in Power Query

after the split instead of transposing use Unpivot, that should solve it

Super User
Super User

Re: Split and Transpose in Power Query

I believe you want this:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjEAAiUdJUM9Rz1PPUMwS8EltTixqCg/JycfyDcyMKgxMjCsMbIAEZYQloFSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Line No_" = _t, #"Row No_" = _t, Description = _t, Totaling = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Line No_", Int64.Type}, {"Row No_", type text}, {"Description", type text}, {"Totaling", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Totaling", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Totaling.1", "Totaling.2", "Totaling.3", "Totaling.4", "Totaling.5"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Totaling.1", Int64.Type}, {"Totaling.2", Int64.Type}, {"Totaling.3", Int64.Type}, {"Totaling.4", Int64.Type}, {"Totaling.5", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {}, "Attribute", "Value")
in
    #"Unpivoted Columns"

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!