How to Structure and Convert Multiple Headers and Merged Cells for Power BI Data Feeds using Power Query Editor

Imagine your boss giving you a dataset like below table and wanting you to include it in the Power BI dashboard and at the same time connecting with your other existing data sources, it would be a headache. Power BI couldn't recognize and read the dataset with multiple headers and merged columns.

Alright, let's get into work on cleaning this data! Let's identify the problems first~

The first problem is that this table has multiple headers.

Second, this table has merged headers as well.

Third, the month is in the first row instead of being in a column.

Here's how I attempt this problematic dataset to make it a feedable dataset for Power BI or other dashboard.

Loading Data to Power Query in Power BI:

First is to import the data into the Power BI and transform the data. My dataset is in Excel workbook, so I would import data from Excel Workbook and click on Transform Data to load the data into Power Query.

Or

Loading Data to Power Query in MS Excel:

Of course, you can do it in Excel as well, if you have Power Query in your Excel. Read more about Power Query availability in Excel over here.

Go to the Data tab > From Table/Range to load the dataset into Power Query for further data transformation.

Create Table pop out would appear, make sure to uncheck "My table has headers" and click on OK.

Transforming Data in Power Query:

The dataset is now loaded into Power Query just like the screenshot below.

Go to Transform tab > Transpose to transpose the entire table.

Now, the table is transposed. The first problem here is that in Column 1, there are null values, the data is not filled in properly.

Right click on the Column 1 > select Fill > click on Down to fill in the data in the null cells in a downwards manner.

Then Column 1 is now properly filled in with the respective month.

Now the attributes are in the first row of the table instead of in a single column.

To convert the attribute row to a column, first, promote the first row/attribute as headers by going to the Home tab > Use First Row as Headers.

Then unpivot the attributes by selecting the first 2 columns that we don't want to unpivot, then click on Unpivot Other Columns.

The unpivoted table look like below. Notice the metric is in a column. This type of table is okay to proceed to making dashboard. However, if the dashboard require calculation such as sum or average the metric, it is better for the metric column to be in the header row.

To convert the metric column to the header row, select on the metric column > go to Transform tab > click on Pivot Column.

In the pop out, in Value Column, choose the value, then click OK.

Finally, the dataset is now properly structured and ready to be the dashboard data feed for further data analysis.

Hope you find this simple article helpful.

Last word from me, if you ever encounter very messy dataset or dashboard, don't get disheartened, continue to seek for solution, you will find it! Have fun analyzing data and learn along the way~