Forum Discussion
Helper ITransform 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
wini_RSolution Supplier
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:
- rsrajkumar
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"
- wini_R
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.
ryan_mayuSuper User
pls see the attachment below
- rsrajkumar
ryan_mayu Thank you very much!
- ryan_mayu
you are welcome