Forum Discussion
Solved
Transpose column instead of rows in Power Query
Thanks Advanced !!! Expected O/P. Have to transpose the last two column into multiple columns. ImkeF
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
Hi . On behalf of Baskar here i'm providing sample data which is parked below.
Could you please help in achiving by taking "Before transpose data" --> "After transpose data"... If possiable please provide us .pbix file whcih will be very helpful.
Before Transpose:
| CONSOLE | SITE_CALL_NAME | MTRB_SHORT_NAME | METER_SHORT_NAME | COMMODITY_ID | TEST_DATE | MF_COUNT | MODIFIED_BY | LINEARITY_DEVIATION_PERC | AVG_of_DENSITY | COMMENTS | FLOW_MAX_PERC |
| Eastern Access | RG | 151-1 | M-11 | PDH | 20Feb2018 | 3 | PISANIK | 0.02 | 915.9 | RATE CURVE | 18 |
| Eastern Access | RG | 151-1 | M-11 | PDH | 20Feb2018 | 3 | PISANIK | 0.02 | 915.9 | RATE CURVE-unable to get the 80%&95% due to rate restrictions. | 36 |
| Eastern Access | RG | 151-1 | M-11 | PDH | 20Feb2018 | 3 | PISANIK | 0.02 | 915.9 | RATE CURVE | 50 |
| Eastern Access | RG | 151-1 | M-12 | PDH | 20Feb2018 | 3 | PISANIK | 0 | 915.9 | RATE CURVE | 19 |
| Eastern Access | RG | 151-1 | M-12 | PDH | 20Feb2018 | 3 | PISANIK | 0 | 915.9 | RATE CURVE | 36 |
| Eastern Access | RG | 151-1 | M-12 | PDH | 20Feb2018 | 3 | PISANIK | 0 | 915.9 | RATE CURVE | 50 |
After Transpose:
| CONSOLE | SITE_CALL_NAME | MTRB_SHORT_NAME | METER_SHORT_NAME | COMMODITY_ID | TEST_DATE | AVG_of_DENSITY | LINEARITY_DEVIATION_PERC | MF_COUNT | Source | Column1 | Column2 | Column3 | Column4 | Column5 | Column6 | Column7 |
| Eastern Access | RG | 151-1 | M-11 | PDH | 20Feb2018 | 915.9 | 0.02 | 3 | FLOW_MAX_PERC | 18 | 36 | 50 | ||||
| Eastern Access | RG | 151-1 | M-11 | PDH | 20Feb2018 | 915.9 | 0.02 | 3 | MODIFIED_BY | PISANIK | PISANIK | PISANIK | ||||
| Eastern Access | RG | 151-1 | M-12 | PDH | 20Feb2018 | 915.9 | 0 | 3 | FLOW_MAX_PERC | 19 | 36 | 50 | ||||
| Eastern Access | RG | 151-1 | M-12 | PDH | 20Feb2018 | 915.9 | 0 | 3 | MODIFIED_BY | PISANIK | PISANIK | PISANIK |
In case of any further information required please let us know. Thanks.
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