Forum Discussion
Transform and Transpose data
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!
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.