Forum Discussion
Baskar
Resident Rockstar
8 years agoTranspose column instead of rows in Power Query
Thanks Advanced !!! Expected O/P. Have to transpose the last two column into multiple columns. ImkeF
- 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
Anonymous
8 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.
ImkeF
Community Champion
8 years agoHi 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.