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.

 

[raskolnikov]

It is truly amazing, four lines of code for something I spent a whole day trying to crack. Bravo @JGordon11

 

[raskolnikov]

I got ChatGPT to explain how the code works which I thought might be nourishing to others on the board:

This is a block of M code written in the Power Query Editor of Microsoft Excel. It performs a series of transformations on a table named "Table1" and returns a new table as the output.

Here is a step-by-step breakdown of the code:

1. Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content] - This line defines a variable named Source and sets it equal to the content of a table named "Table1" in the current Excel workbook.
2. headers = List.Alternate(Record.ToList(Source{0}), 1, 1, 1) - This line defines a variable named headers and sets it equal to a list of column headers for the Source table. The List.Alternate function is used to select every other item in the list starting from the second item.
3. Data = List.Alternate(Table.ToColumns(Source), 1, 1, 0) - This line defines a variable named Data and sets it equal to a list of columns for the Source table. The Table.ToColumns function is used to convert the table to a list of columns, and the List.Alternate function is used to select every other item in the list starting from the first item.
4. Result = Table.FromColumns(Data, headers) - This line defines a variable named Result and sets it equal to a new table created from the Data list and the headers list.
5. in Result - This line specifies that the output of the block of code should be the Result table.

Overall, the code takes a table named "Table1", separates the headers and data into separate lists, selects every other item in each list, and then combines them into a new table with the headers as column names.

 

[JGordon11]

It's close, but not 100%.

items 2 and 3 would be correct if changed as follows:

1. headers = List.Alternate(Record.ToList(Source{0}), 1, 1, 1) - This line defines a variable named headers and sets it equal to a list derived from every other item in the first row of Source. The List.Alternate function is used to skip every other item in the list starting by skipping the second item (identified as 1 since M is zero based).
2. Data = List.Alternate(Table.ToColumns(Source), 1, 1, 0) - This line defines a variable named Data and sets it equal to a list of columns for the Source table. The Table.ToColumns function is used to convert the table to a list of columns, and the List.Alternate function is used to skip every other item in the list starting by skipping the first item (identified as 0 since M is zero based).