Forum Discussion
luxpbi
7 years agoHelper V
Split and Transpose in Power Query
Hi all, I have a table that have columns like this: Line No_ Row No_ Description Totaling 40000 1.A.I.1 1. Desarrollo 200|201|2801|2901|2800 What I need is transform it to lo...
- 7 years ago
after the split instead of transposing use Unpivot, that should solve it
Greg_Deckler
7 years agoCommunity Champion
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"