Power Query: CSV file with no headers has headers and values in alternate columns

[raskolnikov]

I have a CSV resembling the following from our payroll software

Gross Wages	650	Employers NI	100	Employers Pension	30	Week Number	1
Gross Wages	650	Employers NI	100	Employers Pension	30	Week Number	2
Gross Wages	650	Employers NI	100	Employers Pension	30	Week Number	3
Gross Wages	650	Employers NI	100	Employers Pension	30	Week Number	4
Gross Wages	650	Employers NI	100	Employers Pension	30	Week Number	5
Gross Wages	650	Employers NI	100	Employers Pension	30	Week Number	6
Gross Wages	650	Employers NI	100	Employers Pension	30	Week Number	7
Gross Wages	650	Employers NI	100	Employers Pension	30	Week Number	8
Gross Wages	650	Employers NI	100	Employers Pension	30	Week Number	9
Gross Wages	650	Employers NI	100	Employers Pension	30	Week Number	10
Gross Wages	650	Employers NI	100	Employers Pension	30	Week Number	11
Gross Wages	650	Employers NI	100	Employers Pension	30	Week Number	12

As you can see what should be Headers are in columns to the left of their values. I would like to produce a table that looks like this:

Gross Wages	Employers NI	Employers Pension	Week Number
650​	100​	30​	1​
650​	100​	30​	2​
650​	100​	30​	3​
650​	100​	30​	4​
650​	100​	30​	5​
650​	100​	30​	6​
650​	100​	30​	7​
650​	100​	30​	8​
650​	100​	30​	9​
650​	100​	30​	10​
650​	100​	30​	11​
650​	100​	30​	12​

I've tried pivoting, unpivoting, grouping... even ChatGPT couldn't crack it Any help would be appreciated.

 

[DRSteele]

Well, I am no expert but I was able to do this. I brought the data into PowerQuery. There I renamed the relevant columns and removed the others.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", type text}, {"Column4", Int64.Type}, {"Column5", type text}, {"Column6", Int64.Type}, {"Column7", type text}, {"Column8", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column2", "Gross Wages"}, {"Column4", "Employers NI"}, {"Column6", "Employers Pension"}, {"Column8", "Week Number"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Column1", "Column3", "Column5", "Column7"})
in
    #"Removed Columns"

 

[raskolnikov]

Thanks for your input. Thats what I ended up doing yesterday but it would be better to be able to automatically change headers as the actual data set is quite large with many more 'headers' so depending on what I need to analyse, changing every column name manually would be time consuming.

 

[jdellasala]

I have no doubt there are others here that could do this more elegantly, and there is one thing that may create a problem which I'm sure someone could fix.
My solution requires four Queries - one to get the Old Column Names (Column1, etc.), the new Column Names (Gross Wages, etc.), one to merge those tables into a list of lists which is used to rename the columns in the fourth Report query. Here goes.
Original Table:

Headers.xlsx
	A	B	C	D	E	F	G	H
1	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8
2	Gross Wages	650	Employers NI	100	Employers Pension	30	Week Number	1
3	Gross Wages	650	Employers NI	100	Employers Pension	30	Week Number	2
4	Gross Wages	650	Employers NI	100	Employers Pension	30	Week Number	3
5	Gross Wages	650	Employers NI	100	Employers Pension	30	Week Number	4
6	Gross Wages	650	Employers NI	100	Employers Pension	30	Week Number	5
7	Gross Wages	650	Employers NI	100	Employers Pension	30	Week Number	6
8	Gross Wages	650	Employers NI	100	Employers Pension	30	Week Number	7
9	Gross Wages	650	Employers NI	100	Employers Pension	30	Week Number	8
10	Gross Wages	650	Employers NI	100	Employers Pension	30	Week Number	9
11	Gross Wages	650	Employers NI	100	Employers Pension	30	Week Number	10
12	Gross Wages	650	Employers NI	100	Employers Pension	30	Week Number	11
13	Gross Wages	650	Employers NI	100	Employers Pension	30	Week Number	12
Sheet1

OldHeaders:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    UnpivotedOtherColumns = Table.UnpivotOtherColumns(Source, {"Column2", "Column4", "Column6", "Column8"}, "Attribute", "Value"),
    RemovedOtherColumns = Table.RemoveColumns(UnpivotedOtherColumns,{"Attribute", "Value"}),
    AddedOldHeaders = Table.AddColumn(RemovedOtherColumns, "OldHeaders", each Table.ColumnNames( RemovedOtherColumns )),
    RemovedOtherColumns1 = Table.SelectColumns(AddedOldHeaders,{"OldHeaders"}),
    KeptFirstRows = Table.FirstN(RemovedOtherColumns1,1),
    ExpandedOldHeaders = Table.ExpandListColumn(KeptFirstRows, "OldHeaders")
in
    ExpandedOldHeaders

NewHeaders:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    UnpivotedOtherColumns = Table.UnpivotOtherColumns(Source, {"Column1", "Column3", "Column5", "Column7"}, "Attribute", "Value"),
    RemovedColumns = Table.RemoveColumns(UnpivotedOtherColumns,{"Attribute", "Value"}),
    PromotedHeaders = Table.PromoteHeaders(RemovedColumns, [PromoteAllScalars=true]),
    AddedHeadersList = Table.AddColumn(PromotedHeaders, "Headers", each Table.ColumnNames(PromotedHeaders)),
    RemovedOtherColumns = Table.SelectColumns(AddedHeadersList,{"Headers"}),
    KeptFirstRows = Table.FirstN(RemovedOtherColumns,1),
    ExpandedHeaders = Table.ExpandListColumn(KeptFirstRows, "Headers")
in
    ExpandedHeaders

Headers:

let
    Source = OldHeaders,
    AddedIndex = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    AddedCustom = Table.AddColumn(AddedIndex, "New Headers", each NewHeaders{[Index]}),
    ExpandedNewHeaders = Table.ExpandRecordColumn(AddedCustom, "New Headers", {"Headers"}, {"Headers"}),
    RemovedColumns = Table.RemoveColumns(ExpandedNewHeaders,{"Index"}),
    TransposedTable = Table.Transpose(RemovedColumns),
    Custom1 = Table.ToColumns( TransposedTable )
in
    Custom1

FinalReport:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    RemovedOtherColumns = Table.SelectColumns(Source,OldHeaders[OldHeaders]),
    Custom1 = Table.RenameColumns( RemovedOtherColumns, Headers ),
    ChangedType = Table.TransformColumnTypes(Custom1,{{"Gross Wages", Int64.Type}, {"Employers NI", Int64.Type}, {"Employers Pension", Int64.Type}, {"Week Number", Int64.Type}})
in
    ChangedType

Headers.xlsx
	J	K	L	M
1	Gross Wages	Employers NI	Employers Pension	Week Number
2	650	100	30	1
3	650	100	30	2
4	650	100	30	3
5	650	100	30	4
6	650	100	30	5
7	650	100	30	6
8	650	100	30	7
9	650	100	30	8
10	650	100	30	9
11	650	100	30	10
12	650	100	30	11
13	650	100	30	12
Sheet1

The problem here is if/when more columns are added to the source data. However I'm sure someone here can come up with code that can determine the number of columns in the original data and generate a list of odd and even column names to replace the hard coded "ColumnX" references in the code above. It took me a while to figure this much out, even with the help of this video! But it's a start...

 

[JGordon11]

try

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    headers = List.Alternate(Record.ToList(Source{0}), 1, 1, 1),
    deleteCols = List.Alternate(Table.ColumnNames(Source), 1, 1, 1),
    tbl = Table.RemoveColumns(Source, deleteCols),
    Result = Table.RenameColumns(tbl, List.Zip({Table.ColumnNames(tbl), headers}))
in
    Result

 

[jdellasala]

try

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    headers = List.Alternate(Record.ToList(Source{0}), 1, 1, 1),
    deleteCols = List.Alternate(Table.ColumnNames(Source), 1, 1, 1),
    tbl = Table.RemoveColumns(Source, deleteCols),
    Result = Table.RenameColumns(tbl, List.Zip({Table.ColumnNames(tbl), headers}))
in
    Result

Freak'n AMAZING! Thanks for coming to the rescue!

 

[JGordon11]

A shorter (maybe faster) version

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    headers = List.Alternate(Record.ToList(Source{0}), 1, 1, 1),
    Data = List.Alternate(Table.ToColumns(Source), 1, 1, 0),
    Result = Table.FromColumns(Data, headers)
in
    Result

 

[GraH]

Cool stuff by @JGordon11!
But now you take away the fun for me.

 

[raskolnikov]

Beautifully simple solution. I need to learn more DAX/M.

 

[raskolnikov]

I should add this will save me a lot of headaches with similar CSV files we use so thanks a lot.