Forum Discussion
Solved
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 look like this:
| Line No_ | 40000 |
| Row No_ | 1.A.I.1 |
| Description | 1. Desarrollo |
| Account | 200 |
| Account | 201 |
| Account | 2801 |
| Account | 2901 |
| Account | 2800 |
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!
after the split instead of transposing use Unpivot, that should solve it
2 Replies
after the split instead of transposing use Unpivot, that should solve it
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"
