Forum Discussion
Jagen007
2 years agoRegular Visitor
How to transpose column value into header
Greeting
This is my table design is as below.
| Order No. | Material | Delivery no. |
| ABC-11 | 245678 | 400000000 |
| ABC-11 | 245678 | 500000000 |
| ABC-11 | 245678 | 600000000 |
| BBC-12 | 112233 | 200000000 |
| BBC-12 | 112233 | 400000000 |
My desired output is in this table format.
| Order No. | Material | Delivery no. | Delivery no. | Delivery no. |
| ABC-11 | 245678 | 400000000 | 500000000 | 600000000 |
| BBC-12 | 112233 | 200000000 | 400000000 |
Kindly please help to advise how to solve this in Power Query or Power BI.
2 Replies
- wdx223_DanielCommunity Champion
NewStep=Table.Combine(Table.Group(YourTable,{"Order No.","Material"},{"n",each #table({"Order No.","Material"}&List.Transform({1..Table.RowCount(_)},each "Deliver no. "&Text.From(_)),{{[#"Order No."]{0},[Material]{0}}&[#"Deliver no."]})})[n])
- spinfuzerSolution Sage
The easiest way to do it (but not necessarily the fastest or dynamic) is to
1) Group By Order No. and Material With Operation All Rows and name is "All"
2) Add custom column named with formula: Table.Transpose(Table.SelectColumns([All], "Delivery no."))
3) Expand and rename your columns
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRy1jU0VNJRMjIxNTO3ADJMDKBAKVYHm7wpAXkzFHknkLwRUNjQ0MjI2BikkIA8kv2xAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order No." = _t, Material = _t, #"Delivery no." = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order No.", type text}, {"Material", Int64.Type}, {"Delivery no.", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Order No.", "Material"}, {{"All", each _, type table [#"Order No."=nullable text, Material=nullable number, #"Delivery no."=nullable number]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Transpose(Table.SelectColumns([All], "Delivery no."))), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"All"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Column1", "Column2", "Column3"}, {"Column1", "Column2", "Column3"}) in #"Expanded Custom"