Forum Discussion
Pivot columns 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
Hello 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"