Forum Discussion
Transform a CSV file in Power Query where column headers are in separate columns from the values
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.
Hi Anonymous , 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.
2 Replies
- amustafa
Solution Sage
Hi Anonymous , 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.