As Excel users we like to make some really odd looking tables. They make perfect sense to us when we look at them but, when you try and use them as a data source, Power BI struggles to understand them.
At work I recently needed to use an Excel workbook that had a table of data in crosstab format that had multiple header rows. I’m sure you’ve seen tables like it before:
Usually on data like this you’d use the unpivot command to get all of your values into one column but having multiple headers makes this a bit more tricky.
First step when you’ve imported the data to Power Query is to simply use the Transpose tool in the Transform ribbon to turn the table on it’s head.
Next you need merge your header columns; select all of them, right click and select Merge Columns. You will be splitting this back out again so you’ll need to pick a delimiter. Pick the most appropriate one for your data; I have spaces in my first header so using space as a separator would cause me problems.
This replaces the selected columns with one merged column.
[Note: If you use the Merge Columns command from the Add Column ribbon this will add a new column to the end of your table. If you do it this way you will need to move the new column to be the first column and then delete your original columns.
Now transpose the table again and your header row will now be on one merged row. Click Use First Row as Headers under the Transform ribbon to properly turn this into your header.
The next step is to unpivot these columns. The easiest way is to select the column you don’t want to unpivot and then select Unpivot Columns->Unpivot Other Columns from the Transform ribbon.
Now we’re nearly there. We just need to split that column out again by using the Split Column command (it’s in both the Home and the Transform ribbon) and split by your previously selected delimiter.
Job done. Finish off with any other transformations you want to do; renaming columns, changing data types, etc.