Forum Discussion
Pivot columns in query editor
Hi All,
The following table is imported from the database to Power BI desktop.
| ObjNr | CharID | Value_Text | Value_Nr |
| A0102 | NBR_OF_PHASES | 3 | |
| A0102 | MAX_MVA | 25 | 0 |
| A0102 | HIGH_VOLTAGE_RATING_KV | 115/0/0 | 0 |
| A0102 | LOW_VOLTAGE_RATING_KV | 13.09/0/0 | 0 |
| A0102 | XFR_CONFIG | DY | 0 |
| A0102 | HIGH_OPERATING_VOLTAGE | 115 | |
| A0102 | LOW_OPERATING_VOLTAGE | 12.5 | |
| A0104 | NBR_OF_PHASES | 3 | |
| A0104 | MAX_MVA | 20 | 0 |
| A0104 | HIGH_VOLTAGE_RATING_KV | 115/0/0 | 0 |
| A0104 | LOW_VOLTAGE_RATING_KV | 13.09/0/0 | 0 |
| A0104 | XFR_CONFIG | DY | 0 |
| A0104 | HIGH_OPERATING_VOLTAGE | 115 | |
| A0104 | LOW_OPERATING_VOLTAGE | 12.5 |
The Values are given in two columns one is text and the other is a number - the values are mutually exclusive. I want to combine them to one column. Then pivot the columns in such a way that I get the following result:
| ObjNr | NBR_OF_PHASES | MAX_MVA | HIGH_VOLTAGE_RATING_KV | LOW_VOLTAGE_RATING_KV | XFR_CONFIG | HIGH_OPERATING_VOLTAGE | LOW_OPERATING_VOLTAGE |
| A0102 | 3 | 25 | 115/0/0 | 13.09/0/0 | DY | 115 | 12.5 |
| A0104 | 3 | 20 | 115/0/0 | 13.09/0/0 | DY | 115 | 12.5 |
We have another 400+ objects in the table.
Please suggest how to go about the process in Query editor.
Hi kkanda, you can have a look at this solution in Power Query. I'll leave the image of the ouput and M code used. The source used is the table you mentioned above. Thanks
Here's the code used:
let
Source = #table(
{"ObjNr", "CharID", "Value_Text", "Value_Nr"},
{
{"A0102", "NBR_OF_PHASES", null, 3},
{"A0102", "MAX_MVA", null, 25},
{"A0102", "HIGH_VOLTAGE_RATING_KV", "115/0/0", 0},
{"A0102", "LOW_VOLTAGE_RATING_KV", "13.09/0/0", 0},
{"A0102", "XFR_CONFIG", "DY", 0},
{"A0102", "HIGH_OPERATING_VOLTAGE", null, 115},
{"A0102", "LOW_OPERATING_VOLTAGE", null, 12.5},
{"A0104", "NBR_OF_PHASES", null, 3},
{"A0104", "MAX_MVA", null, 20},
{"A0104", "HIGH_VOLTAGE_RATING_KV", "115/0/0", 0},
{"A0104", "LOW_VOLTAGE_RATING_KV", "13.09/0/0", 0},
{"A0104", "XFR_CONFIG", "DY", 0},
{"A0104", "HIGH_OPERATING_VOLTAGE", null, 115},
{"A0104", "LOW_OPERATING_VOLTAGE", null, 12.5}
}
),
Records = Table.TransformRows(
Source,
each _ & [Value_Nr = if [Value_Nr] = 0 then [Value_Text] else [Value_Nr]]
),
FromRec = Table.FromRecords(Records, {"ObjNr", "CharID", "Value_Nr"}),
Grouped = Table.Group(
FromRec,
{"ObjNr"},
{{"AllTable", each _, type table [ObjNr = text, CharID = text, Value_Nr = any]}}
),
Table = Table.TransformColumns(
Grouped,
{"AllTable", each Table.Pivot(_, [CharID], "CharID", "Value_Nr", each _{0})}
),
Pivoted = Table.Combine(Table[AllTable])
in
Pivoted
3 Replies
Hi kkanda, you can have a look at this solution in Power Query. I'll leave the image of the ouput and M code used. The source used is the table you mentioned above. Thanks
Here's the code used:
let
Source = #table(
{"ObjNr", "CharID", "Value_Text", "Value_Nr"},
{
{"A0102", "NBR_OF_PHASES", null, 3},
{"A0102", "MAX_MVA", null, 25},
{"A0102", "HIGH_VOLTAGE_RATING_KV", "115/0/0", 0},
{"A0102", "LOW_VOLTAGE_RATING_KV", "13.09/0/0", 0},
{"A0102", "XFR_CONFIG", "DY", 0},
{"A0102", "HIGH_OPERATING_VOLTAGE", null, 115},
{"A0102", "LOW_OPERATING_VOLTAGE", null, 12.5},
{"A0104", "NBR_OF_PHASES", null, 3},
{"A0104", "MAX_MVA", null, 20},
{"A0104", "HIGH_VOLTAGE_RATING_KV", "115/0/0", 0},
{"A0104", "LOW_VOLTAGE_RATING_KV", "13.09/0/0", 0},
{"A0104", "XFR_CONFIG", "DY", 0},
{"A0104", "HIGH_OPERATING_VOLTAGE", null, 115},
{"A0104", "LOW_OPERATING_VOLTAGE", null, 12.5}
}
),
Records = Table.TransformRows(
Source,
each _ & [Value_Nr = if [Value_Nr] = 0 then [Value_Text] else [Value_Nr]]
),
FromRec = Table.FromRecords(Records, {"ObjNr", "CharID", "Value_Nr"}),
Grouped = Table.Group(
FromRec,
{"ObjNr"},
{{"AllTable", each _, type table [ObjNr = text, CharID = text, Value_Nr = any]}}
),
Table = Table.TransformColumns(
Grouped,
{"AllTable", each Table.Pivot(_, [CharID], "CharID", "Value_Nr", each _{0})}
),
Pivoted = Table.Combine(Table[AllTable])
in
PivotedHello Sundar,
Thank you very much for the tips. I followed the code to bring values to one column and then went through Pivot Column operation and got the following result. I have done the Pivot on CharID and selected the option "Do not aggregate".
The generated code in the Advanced Editor is the following:
let
Source = Excel.Workbook(File.Contents("C:\temp\TestWbook.xlsx"), null, true),
TestWsh_Sheet = Source{[Item="TestWsh",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(TestWsh_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ObjNr", type text}, {"CharID", type text}, {"Value_Text", type any}, {"Value_Nr", type text}}),
#"Records" = Table.TransformRows(
#"Changed Type", each _ & [Value_Nr = if [Value_Nr] = "0" then [Value_Text] else [Value_Nr]]
),
#"Converted to Table" = Table.FromList(Records, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"ObjNr", "CharID", "Value_Text", "Value_Nr"}, {"ObjNr", "CharID", "Value_Text", "Value_Nr"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Column1",{"Value_Text"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Value_Nr", type text}, {"ObjNr", type text}, {"CharID", type text}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[CharID]), "CharID", "Value_Nr")
in
#"Pivoted Column"
