Transform and Transpose data

[rsrajkumar]

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

[wini_R · Accepted solution]

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:

[ryan_mayu · Accepted solution]

@rsrajkumar 

pls see the attachment below

[ryan_mayu · Accepted solution]

@rsrajkumar 

pls see the attachment below

[rsrajkumar · Accepted solution]

@ryan_mayu  Thank you very much!

[ryan_mayu]

you are welcome

[wini_R · Accepted solution]

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.