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
Hi 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.
- ImkeF8 years ago
Community Champion
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.