Forum Discussion
Transpose column instead of rows in Power Query
- 8 years ago
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
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
- Baskar8 years ago
Resident Rockstar
Soory ImkeF i can't able to understand, can u explain me once again .
- ImkeF8 years ago
Community Champion
Hi Baskar,
please upload sample data, that would probably be easier for me than writing long texts.
- Anonymous8 years agoNot applicable
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.