How to dynamically delete multiple header rows in Power BI

Do you know how to dynamically delete multiple header rows in Power BI?

Follow along to the video or check out the steps below.

Have you ever been in a situation that you have an excel file with nested column headers, and you want them to be correctly ingested in your dashboarding tool?

And to make it more difficult, these headers are not at the top of the sheet, they’re in the middle, and they’re not always in a specific row number.

delete the multiple header rows in Power BI

Here is a technique that can help.

Open a new Power BI file and choose to get data from an Excel file.

delete the multiple header rows in Power BI

Choose the SampleData.xlsx

delete the multiple header rows in Power BI

Select the Sheet 1 (even though there are suggested tables, in this case, the aim is to learn how to do this by yourself) and click on Transform Data.

delete the multiple header rows in Power BI

Right click on the last step on the Applied Steps section and select Insert Step After.

delete the multiple header rows in Power BI

Rename the new step as “Original State”.

delete the multiple header rows in Power BI

Create a new index column, starting from 0

Now you filter any of the columns, so you end up with the top row that contains some of the header information.
This is how it looks before filtering:

delete the multiple header rows in Power BI

The top row that we want as a header, is the one containing Date, Education, Energy, Gas, Rent, etc. So, we’ll filter for the column 3 for the rows containing “Energy” (you could filter using any of the columns, as long as you end up with the row where the header should start):

multiple header rows

Now select the Index column at the end of the table, right click it and select “Remove other Columns”:

delete the multiple header rows in Power BI

Still in this step, add “{0}[Index]” in the end of the command and rename this step to “Rows to remove”. It will look like this:

delete the multiple header rows in Power BI

Right click the “Rows to remove” step and select “Insert step after”.
Rename it to “Back to Original State” and in the command section, change it to:

= #”Original State”
Like the following image:

Insert another step after “Back to Original State” and rename it to “Removing top N  rows” and type the below command.
This will make Power BI remove the top number defined on the step “Rows to Remove”.

delete the multiple header rows in Power BI

Now we have to merge some rows, so we get the full desired header. The same technique will be applicable for as many rows as you want in the header.

delete the multiple header rows in Power BI

Click on Transform menu and then Transpose:

Now all the rows became columns.
We’ll merge the columns 1 and 2 (and other columns if you wanted more than 2 rows merged).
Select the ones you want to merge (holding Ctrl), right click it and select Merge Columns, and leave all the default options when the option window pops up and click OK.

multiple header rows

Click to Transpose the table again.

multiple header rows

Now we’ll go to the Home menu and then “Use First Row as Headers”:

multiple header rows

Now you can use the table with the correct headers.

Adapt these steps to make different outputs, like isolating the header in another table and then appending the headers with the rest of the table and using the first row as headers.

How to guides for Power BI beginners