Very weird report output requires a transformation in PowerQuery;

[Lurkily]

I have a report output formatted something like this:

Global User Defined Field Label 01	Global User Field 01	Global User Defined Field Label 02	Global User Field 02	Global User Defined Field Label 03	Global User Field 03	Global User Defined Field Label 04	Global User Field 04	Global User Defined Field Label 05
SITE TYPE		USER LAST NAME	Boberson	USER FIRST NAME	Bob	USER MIDDLE INITIAL	B	USER STATUS
SITE TYPE		COMPUTER NAME	Dell-XYZ	USER LAST NAME	Boberson	USER FIRST NAME	Bob	USER MIDDLE INITIAL

As you can see, it's dumping the column header into one column, and the value in the next. This wouldn't be too problematic, but not every item in the list has the same data entries present, so sometimes the second column is the site type, sometimes it's computer name. Sometimes the fourth is computer name, sometimes it's first name.

I know how to do this formulaically - make a column with the name I want, then do an Xlookup across this row for the column name, and pull the results from a matching array displaced one cell to the right. But I'm not sure how to do this in PowerQuery's scripting language, and I can't see a pre-built way to make this behavior work by using menu selections.

If anybody has some advice, I'd appreciate the help. I can reformat this all formulaically, but the dataset is thousands of lines, and I suspect this will make it much slowed to work with than using a query.

 

[JGordon11]

I tried this on 10000 records and it took about 2 seconds. Change the Source step to retrieve your data table.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddRecordColumn = Table.AddColumn(Source, "Custom", each Record.Combine(List.RemoveNulls(List.Transform(List.Split(Record.ToList(_),2), (x)=> try Record.FromList({x{1}}, {x{0}}) otherwise null)))),
    Result = Table.FromRecords(AddRecordColumn[Custom])
in
    Result

 

[Kaper]

I tried m code proposed above and it didn't work (as it showed later) because in sample data there was missing Global User Field 05 column.
Then when I added this column to test my approach it showed that JGordon11 code works fine

Anyway, As I really almost clicked out the whole soultion, I'll show it too. As a matter of fact I clicked out just for 2 first pairs of columns, then used Advanced editor to copy pairs of steps, and to include larger namber of tables in Combine stage.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    IndexAdded = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    RemovedBut01 = Table.SelectColumns(IndexAdded,{"Global User Defined Field Label 01", "Global User Field 01", "Index"}),
    Changed01 = Table.RenameColumns(RemovedBut01,{{"Global User Defined Field Label 01", "Global User Defined Field Label"}, {"Global User Field 01", "Global User Field"}}),
    RemovedBut02 = Table.SelectColumns(IndexAdded,{"Global User Defined Field Label 02", "Global User Field 02", "Index"}),
    Changed02 = Table.RenameColumns(RemovedBut02,{{"Global User Defined Field Label 02", "Global User Defined Field Label"}, {"Global User Field 02", "Global User Field"}}),
    RemovedBut03 = Table.SelectColumns(IndexAdded,{"Global User Defined Field Label 03", "Global User Field 03", "Index"}),
    Changed03 = Table.RenameColumns(RemovedBut03,{{"Global User Defined Field Label 03", "Global User Defined Field Label"}, {"Global User Field 03", "Global User Field"}}),
    RemovedBut04 = Table.SelectColumns(IndexAdded,{"Global User Defined Field Label 04", "Global User Field 04", "Index"}),
    Changed04 = Table.RenameColumns(RemovedBut04,{{"Global User Defined Field Label 04", "Global User Defined Field Label"}, {"Global User Field 04", "Global User Field"}}),
    RemovedBut05 = Table.SelectColumns(IndexAdded,{"Global User Defined Field Label 05", "Global User Field 05", "Index"}),
    Changed05 = Table.RenameColumns(RemovedBut05,{{"Global User Defined Field Label 05", "Global User Defined Field Label"}, {"Global User Field 05", "Global User Field"}}),
    AddedTogether = Table.Combine({Changed01, Changed02, Changed03, Changed04, Changed05}),
    RenovedNull = Table.SelectRows(AddedTogether, each [Global User Field] <> null and [Global User Field] <> ""),
    RePivot = Table.Pivot(RenovedNull, List.Distinct(RenovedNull[#"Global User Defined Field Label"]), "Global User Defined Field Label", "Global User Field"),
    RemovedHelperColumn = Table.RemoveColumns(RePivot,{"Index"})
in
    RemovedHelperColumn

BTW. It would be much shorter if For-Next loop would be available in m language. But it isn't. And I know that some tricks could be done with List.Generate or recursive functions, but it's beyond my knowledge
And I'll try to understand this a bit long line with inner function (looks appealing )

AddRecordColumn = Table.AddColumn(Source, "Custom", each Record.Combine(List.RemoveNulls(List.Transform(List.Split(Record.ToList(_),2), (x)=> try Record.FromList({x{1}}, {x{0}}) otherwise null)))),

But in the evening, as I'm just going offline.