Users can remove empty rows and promote headers in Power BI using the Power Query Editor. You can skip a set number of rows at the top of input data, or setup data transformations to dynamically skip rows that are headers in incoming data. After skipping unnecessary rows in Power BI, users can promote a row to the header position.
Header names are a vitally important part of data preparation in Power BI. They become the titles and reference points for future DAX formulas and make a dataset infinitely easier to work with later on.
While you could manually rename all of the columns in a Power BI dataset, it’s much faster the let Power Query do the hard work for you.
We’ll take a look at both how to remove unnecessary rows for headers and how to promote a row to headers.
Let’s jump in!
Table of Contents
How to Promote Headers in Power BI
Data perpetration and data modeling in Power BI must be done using the Power Query editor. Once you bring data into Power BI, you can launch the Power Query Editor, Microsoft’s easy to use data preparation tool, and select “Use First Row as Headers” from the ribbon at the top of the screen.
We’ll look at this process in more detail, and also walk through how to approach preparing data in Power BI when you need to skip certain rows at the top of a dataset prior to promoting headers.
Launching the Power Query Editor in Power BI
The Power Query Editor can either be launched by selecting “Transform Data” at the top of the Power BI ribbon when it’s in Report View, or you can right click on a table on the right side of the screen and select “Edit Query“
Both Options work equally well, and will take you to the Power Query Editor.
Remove Header Rows from Power BI Input Data
Before promoting headers, it’s very common to need to remove rows at the top of incoming data. After opening the Power Query editor, go to the Home section, and select Remove Top Rows.
The Remove Top Rows screen will appear, with a prompt asking how many rows you want to skip. Count the number of rows above the head row.
In our example, we will skip 4 rows, which will setup our headers on Row 1.
After the desired row of header information is on row one, we can promote our headers to column titles.
Promoting the First Row to Headers in Power BI
After skipping unnecessary rows at the top of an incoming dataset, select Use First Row as Headers from the Home section of the Power Query Ribbon. This option will move the data in row #1 to the title position of each column.
You can double click on any column name, if you need to further overwrite the newly promoted column name.
Note: The Use First Row as Headers drop down menu in Power Query also has the option to Use Headers as First Row in times that you want to demote headers.
Tips and Tricks for Working with Promoted Headers in Power BI
When working with headers in Power BI, there are a few considerations to keep in mind. The most common scenario of needing to remove headers is when using a report from a separate system that has to be run manually and saved somewhere that Power BI can access it.
Here are some additional considerations, tips and tricks that can save you time.
Connect Directly to Source Systems
Power BI has the capability to connect to over 200 different source systems. The supported connections range from cloud services, to many popular accounting systems, databases and data warehouses.
We recommend establishing a direct connection to the source system whenever possible. It generally takes more setup time up front and some coordination with your IT department, but can really be a big time saver. The drawback is that even when you do establish a direct connection it can take some work to rebuild the behind the scenes data into a report format that you are used to see.
For older systems that do not allow for direct connections, there are RPA solutions such as Power Automate Desktop that let you program a bot to run reports out of a system like a human would.
It can be a bit intimidating to connect to databases and other systems. But it’s not as difficult as you might think and as Patrick explains in the video below Power BI can do a lot of heavy lifting for you!
Using Keep Headers to Retain Important Information
In addition to removing rows, Power BI gives you the option to Keep Rows. If a header contains specific information that you need later in a report such as the date the report was run or the corporate entity that it relates to, duplicate the incoming dataset, and use the Keep Top Rows function available on the Home Tab next to Remove Rows.
The dataset can later be joined or used to setup a relationship for use in your reporting dashboard.
Using Auto Detect Data Type After Promoting Headers
After promoting headers, Power BI will automatically detect Data Types in most cases. If it does not detect the new data types automatically, you can select Detect Data Type from the Transform Tab of the Power BI Ribbon.
To manually override the automatic data type detection, you can right click on a column header and define the new data type.
Dynamically Remove Rows vs Skipping a Set Number of Rows in Power BI
If incoming data changes over time, it may be necessary to tell Power BI to dynamically skip top rows based on a value in a column. Use a column filter to tell Power BI to dynamically skip rows. Find a column that is completely full of null values at the top, click on the column filter, and de-select null.
This will remove all empty rows at the top of a dataset and prevent Power Query from failing if the number of header rows changes over time.
When choosing a column to filter out null rows, choose one that is unlikely to have data entered into it. If you’re using a system generated report as a starting point it should be consistent.
However, if you are using an Excel or CSV file prepared by another person or department, they will sometimes add notes to random columns which will cause a dynamic filter to remove nulls to fail.
Conclusion
To promote headers in Power BI, use the Use First Row as Headers option in the Home section of Power Query. When needing to first remove several rows at the top of an incoming dataset, Power Query has the ability to Remove Rows.
You can define a specific number of rows to skip at the top of a dataset prior to promoting headers. Alternatively, you can use a column filter to filter out null values. Determining which one to use will depend on your use case.
If a report runs directly out of a source system, it should be consistent over time. When the data comes from another person or department it’s possible that they will edit the data before sending it to you. This means that dynamic filtering could be more appropriate.
As a best practice, connecting directly to a database, accounting system, or other software will ensure consistency and minimize the need for data preparation.