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.
Here is a technique that can help.
Open a new Power BI file and choose to get data from an Excel file.
Choose the SampleData.xlsx
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.
Right click on the last step on the Applied Steps section and select Insert Step After.
Rename the new step as “Original State”.
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:
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):
Now select the Index column at the end of the table, right click it and select “Remove other Columns”:
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:
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”.
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.
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.
Click to Transpose the table again.
Now we’ll go to the Home menu and then “Use First Row as Headers”:
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.