Forum Discussion
Transform Table from vertical to horizontal
Hi DaubPitt ,
Below are the setps.
1.Select two columns(Hold down the Ctrl key while the mouse selects.), then click 'Unpivot Columns'.
2.Remove the 'Attribute' column.
3.Change the 'Value' column to text type.
4.Group on ’Issue-No‘, choose some dummy operation for FULLNAME (e.g. Max, not "All Rows") and then adjust the generated code to combine the names.
5.Spilt the column with ";".
Result:
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc5NDgARDAXgu1iLqCqZ5ZxD3P8a059BLSQv30M7RoCCIQbg80KYcUPKuSpqOEURLB74wqOo4RRdsHtIGXWQBSka1Xv6D/wVrunoCsUL+II+tyAFVgESJA9rT9p7WtEEm4cE9qeFOT8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Issue-No." = _t, #"Step-No" = _t, #"Step-No-Desc" = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Issue-No."}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Issue-No."}, {{"Column", each Text.Combine([Value],";"), type any}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "Column", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), List.Transform({1..List.Max(Table.AddColumn(#"Grouped Rows", "Temp", each List.Count(Text.Split([Column],";")))[Temp])},each "Column " & Number.ToText(_)))
in
#"Split Column by Delimiter"
First of all, thank you very much for your quick support 🙂
I did the steps as you describe, but it does'nt work as I expected. See attached screenshot.
In this case, I only used the first row of source code.
"Source = Table.FromRows(Json.Document(Binary.Decompress(..."
Why the Issue-No are different from Issue-No in my table?
Hi DaubPitt ,
Below are the setps.
1.Select two columns(Hold down the Ctrl key while the mouse selects.), then click 'Unpivot Columns'.
2.Remove the 'Attribute' column.
3.Change the 'Value' column to text type.
4.Group on ’Issue-No‘, choose some dummy operation for FULLNAME (e.g. Max, not "All Rows") and then adjust the generated code to combine the names.
5.Spilt the column with ";".
Result:
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Wow, thank you !
Since your Excel doesn't contain any sensitive info, could you please upload the file without confidential/sensitive data to a cloud storage service such as Onedrive/Google Drive/Dropbox/Box (Onedrive preferred) and share the link here?
