Pivot columns in query editor

[kkanda]

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. 

[kkanda]

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"

[SundarRaj]

@kkanda Yes, I think that is a better last step. Cheers!

[SundarRaj · Accepted solution]

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