Forum Discussion
Baskar
Resident Rockstar
Resident RockstarTranspose 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
ImkeF
Community Champion
Community ChampionHi Baskar,
please upload sample data, that would probably be easier for me than writing long texts.
As requested here I'm providing sample data which is parked below.
We are looking for using Before transpose data and need to get output like After tranpose data.
ImkeF possiable please provide .pbix file would help us alot.
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 Athabasca EC 101-1 M-11 CDB 20Jan2018 4 LADOUCER 0.03 913.9 45 Athabasca EC 101-1 M-11 CDB 20Jan2018 4 LADOUCER 0.03 913.9 RATE CURVE 63 Athabasca EC 101-1 M-11 CDB 20Jan2018 4 LADOUCER 0.03 913.9 RATE CURVE 80 Athabasca EC 101-1 M-11 CDB 20Jan2018 4 LADOUCER 0.03 913.9 RATE CURVE 95 Athabasca EC 101-1 M-12 CDB 20Jan2018 4 LADOUCER 0.01 914.3 RATE CURVE 37 Athabasca EC 101-1 M-12 CDB 20Jan2018 4 LADOUCER 0.01 914.3 RATE CURVE 59 Athabasca EC 101-1 M-12 CDB 20Jan2018 4 LADOUCER 0.01 914.3 RATE CURVE 78 Athabasca EC 101-1 M-12 CDB 20Jan2018 4 LADOUCER 0.01 914.3 RATE CURVE 94 Athabasca EC 101-1 M-13 CDB 20Jan2018 4 LADOUCER 0.05 914.3 RATE CURVE 36 Athabasca EC 101-1 M-13 CDB 20Jan2018 4 LADOUCER 0.05 914.3 RATE CURVE 59 Athabasca EC 101-1 M-13 CDB 20Jan2018 4 LADOUCER 0.05 914.3 RATE CURVE 80 Athabasca EC 101-1 M-13 CDB 20Jan2018 4 LADOUCER 0.05 914.3 RATE CURVE 94 Athabasca EC 101-1 M-14 CDB 21Jan2018 4 BARRE 0.05 913.9 RATE CURVE 43 Athabasca EC 101-1 M-14 CDB 21Jan2018 4 BARRE 0.05 913.9 RATE CURVE 62 Athabasca EC 101-1 M-14 CDB 21Jan2018 4 BARRE 0.05 913.9 RATE CURVE 82 Athabasca EC 101-1 M-14 CDB 21Jan2018 4 BARRE 0.05 913.9 RATE CURVE 97 Athabasca TT 152-1 M-11 BHB 29Jan2018 5 BENNETM3 0.05 913.8 RATE CURVE 20 Athabasca TT 152-1 M-11 BHB 29Jan2018 5 BENNETM3 0.05 913.8 RATE CURVE 41 Athabasca TT 152-1 M-11 BHB 29Jan2018 5 BENNETM3 0.05 913.8 RATE CURVE 61 Athabasca TT 152-1 M-11 BHB 29Jan2018 5 BENNETM3 0.05 913.8 RATE CURVE 83 Athabasca TT 152-1 M-11 BHB 29Jan2018 5 BENNETM3 0.05 913.8 RATE CURVE 95 Athabasca TT 152-1 M-12 BHB 30Jan2018 5 BENNETM3 0.07 913.8 RATE CURVE 21 Athabasca TT 152-1 M-12 BHB 30Jan2018 5 BENNETM3 0.07 913.8 RATE CURVE 40 Athabasca TT 152-1 M-12 BHB 30Jan2018 5 BENNETM3 0.07 913.8 RATE CURVE 59 Athabasca TT 152-1 M-12 BHB 30Jan2018 5 BENNETM3 0.07 913.8 RATE CURVE 81 Athabasca TT 152-1 M-12 BHB 30Jan2018 5 BENNETM3 0.07 913.8 RATE CURVE 96 Athabasca TT 152-1 M-13 BHB 30Jan2018 5 BENNETM3 0.09 913.7 RATE CURVE 20 Athabasca TT 152-1 M-13 BHB 30Jan2018 5 BENNETM3 0.09 913.7 RATE CURVE 40 Athabasca TT 152-1 M-13 BHB 30Jan2018 5 BENNETM3 0.09 913.7 RATE CURVE 60 Athabasca TT 152-1 M-13 BHB 30Jan2018 5 BENNETM3 0.09 913.7 RATE CURVE 81 Athabasca TT 152-1 M-13 BHB 30Jan2018 5 BENNETM3 0.09 913.7 RATE CURVE 97 Athabasca TT 152-1 M-14 BHB 30Jan2018 4 BENNETM3 0.09 913.7 RATE CURVE 21 Athabasca TT 152-1 M-14 BHB 30Jan2018 4 BENNETM3 0.09 913.7 RATE CURVE 41 Athabasca TT 152-1 M-14 BHB 30Jan2018 4 BENNETM3 0.09 913.7 RATE CURVE 61 Athabasca TT 152-1 M-14 BHB 30Jan2018 4 BENNETM3 0.09 913.7 RATE CURVE 94 Athabasca TT 152-1 M-15 BHB 30Jan2018 5 BENNETM3 0.03 913.3 RATE CURVE 21 Athabasca TT 152-1 M-15 BHB 30Jan2018 5 BENNETM3 0.03 913.3 RATE CURVE 41 Athabasca TT 152-1 M-15 BHB 30Jan2018 5 BENNETM3 0.03 913.3 RATE CURVE 59 Athabasca TT 152-1 M-15 BHB 30Jan2018 5 BENNETM3 0.03 913.3 RATE CURVE 80 Athabasca TT 152-1 M-15 BHB 30Jan2018 5 BENNETM3 0.03 913.3 RATE CURVE 96 After Appling transpose data sould look like below
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 Athabasca EC 101-1 M-11 CDB 20Jan2018 913.9 0.03 4 FLOW_MAX_PERC 45 63 80 95 Athabasca EC 101-1 M-11 CDB 20Jan2018 913.9 0.03 4 MODIFIED_BY LADOUCER LADOUCER LADOUCER LADOUCER Athabasca EC 101-1 M-12 CDB 20Jan2018 914.3 0.01 4 FLOW_MAX_PERC 37 59 78 94 Athabasca EC 101-1 M-12 CDB 20Jan2018 914.3 0.01 4 MODIFIED_BY LADOUCER LADOUCER LADOUCER LADOUCER Athabasca EC 101-1 M-13 CDB 20Jan2018 914.3 0.05 4 FLOW_MAX_PERC 36 59 80 94 Athabasca EC 101-1 M-13 CDB 20Jan2018 914.3 0.05 4 MODIFIED_BY LADOUCER LADOUCER LADOUCER LADOUCER Athabasca EC 101-1 M-14 CDB 21Jan2018 913.9 0.05 4 FLOW_MAX_PERC 43 62 82 97 Athabasca EC 101-1 M-14 CDB 21Jan2018 913.9 0.05 4 MODIFIED_BY BARRE BARRE BARRE BARRE Athabasca TT 152-1 M-11 BHB 29Jan2018 913.8 0.05 5 FLOW_MAX_PERC 20 41 61 83 95 Athabasca TT 152-1 M-11 BHB 29Jan2018 913.8 0.05 5 MODIFIED_BY BENNETM3 BENNETM3 BENNETM3 BENNETM3 BENNETM3 Athabasca TT 152-1 M-12 BHB 30Jan2018 913.8 0.07 5 FLOW_MAX_PERC 21 40 59 81 96 Athabasca TT 152-1 M-12 BHB 30Jan2018 913.8 0.07 5 MODIFIED_BY BENNETM3 BENNETM3 BENNETM3 BENNETM3 BENNETM3 Athabasca TT 152-1 M-13 BHB 30Jan2018 913.7 0.09 5 FLOW_MAX_PERC 20 40 60 81 97 Athabasca TT 152-1 M-13 BHB 30Jan2018 913.7 0.09 5 MODIFIED_BY BENNETM3 BENNETM3 BENNETM3 BENNETM3 BENNETM3 Athabasca TT 152-1 M-14 BHB 30Jan2018 913.7 0.09 4 FLOW_MAX_PERC 21 41 61 94 Athabasca TT 152-1 M-14 BHB 30Jan2018 913.7 0.09 4 MODIFIED_BY BENNETM3 BENNETM3 BENNETM3 BENNETM3 Athabasca TT 152-1 M-15 BHB 30Jan2018 913.3 0.03 5 FLOW_MAX_PERC 21 41 59 80 96 Athabasca TT 152-1 M-15 BHB 30Jan2018 913.3 0.03 5 MODIFIED_BY BENNETM3 BENNETM3 BENNETM3 BENNETM3 BENNETM3 Athabasca TT 152-1 M-16 BHB 30Jan2018 913.1 0.04 5 FLOW_MAX_PERC 21 41 61 80 95 Athabasca TT 152-1 M-16 BHB 30Jan2018 913.1 0.04 5 MODIFIED_BY BENNETM3 BENNETM3 BENNETM3 BENNETM3 BENNETM3 In case any further information required please do let me know.
- ImkeF
Hi Anonymous and Baskar,
just saw that you uploaded new data.
The solution I gave will adjust automatically do more rows/new columns.
Just replace the reference in the first step "Source" to your data and you're done.
I will not upload any pbix, the code I gave contains the solution.
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.
- 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