Transform a CSV file in Power Query where column headers are in separate columns from the values

[AdrianJ]

Hey folks,

I've tried to figure this out myself and searched everywhere for a solution, to no avail. I am exporting reports in CSV format from Microsoft Dynamics 2016 and it outputs the report data in a very annoying format:

Table0_Header0,Table0_Header2,Table0_Header4,Table0_Header5,Table0_Details0,Table0_Details2,Table0_Details4,Table0_Details5
Name,Age,Gender,Location,John Doe,23,Male,Someplace
Name,Age,Gender,Location,Mary Sue,49,Female,Nowhere
Name,Age,Gender,Location,Bob Joans,37,Male,Outhere

How do I convert this structure into a sensible table programtically using Power Query? Like so:

Name	Age	Gender	Location
John Doe	23	Male	Someplace
Mary Sue	49	Female	Nowhere
Bob Joans	37	Male	Outhere

I have a lot of reports that are exported so the solution needs to work for different numbers of Header+Details sets, e.g., there could be 4 header+details sets (like here), or 8, or 20, etc. 

[amustafa · Accepted solution]

Hi @AdrianJ , See the Solution in the attached .pbix file. Look at the Power Query section for transformation steps. I seperated the headers and details then appended the detail rows to header. This should work for any number of columns you may have in your output files.

[AdrianJ]

Fantastic amustafa, thank you very much!

Splitting it out in a Header table and Detail table and then recombining it was an inspired approach. Thank you again for the help :).