Forum Discussion
Baskar
8 years agoResident Rockstar
Transpose column instead of rows in Power Query
Thanks Advanced !!!
Input Data
Expected O/P.
Have to transpose the last two column into multiple columns.
Output Data
Please paste this code into the advanced editor and follow the steps:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vZBPC8IwDMW/Sijsto620rEeh04dosj8c5k7bDPoQDZou+9v610YiLvkvSSQX3hlSbLaWNQ9pG2LxpCQFBtXuOSUO91T7uW42roqGF1jQ3ni/MJP81N6yHfOsYgJJ4rLSPkT6TmD5aW4Zv5UQqrw3xw69nXzQrADPNCCfSIkLLiNjIlYyQDu42ena4ug0VjdtbYbehN5RDzDg66RbBpHTOF8TVvNAJma2E8QH1f1Bg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [CONSOLE = _t, SITE_CALL_NAME = _t, MTRB_SHORT_NAME = _t, METER_SHORT_NAME = _t, COMMODITY_ID = _t, TEST_DATE = _t, MF_COUNT = _t, MODIFIED_BY = _t, LINEARITY_DEVIATION_PERC = _t, AVG_of_DENSITY = _t, COMMENTS = _t, FLOW_MAX_PERC = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"CONSOLE", type text}, {"SITE_CALL_NAME", type text}, {"MTRB_SHORT_NAME", type text}, {"METER_SHORT_NAME", type text}, {"COMMODITY_ID", type text}, {"TEST_DATE", type date}, {"MF_COUNT", Int64.Type}, {"MODIFIED_BY", type text}, {"LINEARITY_DEVIATION_PERC", type number}, {"AVG_of_DENSITY", type number}, {"COMMENTS", type text}, {"FLOW_MAX_PERC", Int64.Type}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"AVG_of_DENSITY", "COMMENTS"}), #"Grouped Rows" = Table.Group(#"Removed Columns", {"SITE_CALL_NAME", "MTRB_SHORT_NAME", "METER_SHORT_NAME", "COMMODITY_ID", "TEST_DATE", "MF_COUNT", "LINEARITY_DEVIATION_PERC"}, {{"All", each _, type table}, {"CountRows", each Table.RowCount(_), type number}}), AddTranspose = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Transpose(Table.DemoteHeaders(Table.SelectColumns([All], {"MODIFIED_BY", "FLOW_MAX_PERC"})))), DynamicExpand = Table.ExpandTableColumn(AddTranspose, "Custom", List.Transform({1..List.Max(AddTranspose[CountRows])}, each "Column"&Text.From(_))), Cleanup = Table.RemoveColumns(DynamicExpand,{"All", "CountRows"}) in Cleanup
7 Replies
- ImkeFCommunity Champion
Why? Your data has the perfect shape for the data model.
1) Group on all columns that shall not be transposed and select "All". That will return partitions of you table
2) Remove all columns from those tables that shall not be transposed from the partitions
3) Demote headers and transpose the partitions