Forum Discussion
Transform Table from vertical to horizontal
Hello Community,
I read data from Excel files files that are stored in different folders. Every Excel file has the same structure. I performed some power query steps (as a beginner) to generate a table that's look good for my needs. But there is one thing, that I have to change. See screenshot.
How can I do this? I like to use the Excel data later for creating a Share Point List and the "Issue-No." will be the "Primary Key" = ID.
Thank you in advance for any tips.
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.
6 Replies
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.
NewStep=Table.Combine(Table.Group(PreviousStepName,"Issue-No.",{"n",each #table(null,List.Combine(Table.ToRow(_)))})[n])
