Forum Discussion
Transform and Transpose data
Hi,
I have loaded the below data form an excel file:
| Column 1 | MTD | YTD |
| Orders | ||
| A | 3.0 | 5.0 |
| B | 8.0 | 10.0 |
| C | 2.0 | 3.0 |
| D | 7.0 | 8.0 |
| Sales | ||
| A | 7.0 | 9.0 |
| B | 15.0 | 17.0 |
| C | 3.0 | 6.0 |
| D | 5.0 | 7.0 |
| Profit | ||
| A | 6.0 | 7.0 |
| B | 4.0 | 6.0 |
| C | 2.0 | 4.0 |
| D | 1.0 | 3.0 |
I would like to transform and transpose the data to the below format:
| Category | Product | MTD | YTD |
| Orders | A | 3.0 | 5.0 |
| B | 8.0 | 10.0 | |
| C | 2.0 | 3.0 | |
| D | 7.0 | 8.0 | |
| Sales | A | 7.0 | 9.0 |
| B | 15.0 | 17.0 | |
| C | 3.0 | 6.0 | |
| D | 5.0 | 7.0 | |
| Profit | A | 6.0 | 7.0 |
| B | 4.0 | 6.0 | |
| C | 2.0 | 4.0 | |
| D | 1.0 | 3.0 |
Please let me know how this can be done
Thanks
Hey rsrajkumar,
Your input table is a single table or there are 3 separate ones? If the former, then you can try these steps in Power Query (just paste the code in Advanced Editor to see it in action):
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8i9KSS0qVtJRUgDjWJ1oJUcgy1jPAEiaAkmQiBOQbQEWMTSACjkDOUZgIWOoiAuQbQ4WsYCKKKAYG5yYk4ppEUSHJZJFhqYQm8yRbII4xwzJJogic6w2BRTlp2WWYFhlhqIFZJUJirEIL5kgWWSI8GQsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, MTD = _t, YTD = _t]), #"Added Custom" = Table.AddColumn(Source, "Category", each if [Product] = "Orders" or [Product] = "Sales" or[Product] = "Profit" then [Product] else null), #"Filled Down" = Table.FillDown(#"Added Custom",{"Category"}), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Product] <> " " and [Product] <> "Sales" and [Product] <> "Orders" and [Product] <> "Profit")), #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Category", "Product", "MTD", "YTD"}) in #"Reordered Columns"Output table:
pls see the attachment below
ryan_mayu Thank you very much!
6 Replies
Hey rsrajkumar,
Your input table is a single table or there are 3 separate ones? If the former, then you can try these steps in Power Query (just paste the code in Advanced Editor to see it in action):
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8i9KSS0qVtJRUgDjWJ1oJUcgy1jPAEiaAkmQiBOQbQEWMTSACjkDOUZgIWOoiAuQbQ4WsYCKKKAYG5yYk4ppEUSHJZJFhqYQm8yRbII4xwzJJogic6w2BRTlp2WWYFhlhqIFZJUJirEIL5kgWWSI8GQsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, MTD = _t, YTD = _t]), #"Added Custom" = Table.AddColumn(Source, "Category", each if [Product] = "Orders" or [Product] = "Sales" or[Product] = "Profit" then [Product] else null), #"Filled Down" = Table.FillDown(#"Added Custom",{"Category"}), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Product] <> " " and [Product] <> "Sales" and [Product] <> "Orders" and [Product] <> "Profit")), #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Category", "Product", "MTD", "YTD"}) in #"Reordered Columns"Output table:
Hi wini_R , Thanks for your quick response. It is from a single table. What is the significance of the text that follows "Binary.FromText"
Hey rsrajkumar,
For your final solution it doesn't matter, that step should be replaced with your source data. It is here to simplify code sharing between users - anyone can grab it and use in their power query editor without the need to load/prepare some sample data. Essentially it is your input table encoded to Base64 format.
pls see the attachment below
ryan_mayu Thank you very much!
you are welcome