How to Skip Rows and Promote Headers in Power Query

Power Query gives users a number of ways to skip rows and promote headers, from skipping a set number of rows to more dynamic solutions for times when header data isn’t as consistent as you would like it to be.

Skipping the first rows of a document and promoting headers is a common task in Power Query

The combination of skipping rows at the top of a document and promoting the remaining headers is a valuable skill that’s especially useful for people working with finance and accounting use cases.

Let’s dive in!

How to Import Data into Power Query

The method for importing data into Power Query will depend on the data source and the program you’re importing it into. Excel, Power BI, Dat Flows Gen2 and other solutions have similar Power Query interfaces.

When importing data into Power Query for Excel, select the data to import, go to the Data tab and click on From Table/Range or use the Get Data function to import a document or connect to a data source.

Getting data from a table/range in Excel to import into Power Query

A popular method is using Power Query to import a folder full of files and then using Power Query to format the data into one continuous table for analysis.

How to Set a Header Row in Power Query

To set a specific row as the header in Power Query, you have to combine two steps. First skip the unwanted rows at the top of a data set, and then tell power Query to “Promote Headers”. Skipping the first rows can be done based on number of rows or dynamically.

Skipping First N Rows in Power Query

To skip the first rows in Power Query, import your data, go to the ‘Home’ tab in the Power Query Editor, click on “Remove Rows”, select “Remove Top Rows” Then specify the number of rows you wish to skip.

Removing Top Rows from a document using Power Query

The results of skipping the first rows of a dataset will place headers on the top row, or row #1 of your dataset. Headers need to be in this position to be promoted to headers. Prior to being promoted Power Query will use place holder column names such as Column1, Column2, etc.

Screenshot of headers in the first row that still need to be promoted

If you’re working with inconsistent datasets where header rows can vary in the number of rows the take at the top of a data set it may be useful to dynamically remove them.

Dynamically Skip Rows in Power Query

To dynamically skip rows in Power Query based to remove headers, use a Filter on a column that’s unlikely to have data in it. Click on the filter button on the header of a column and unselect null or empty values. If a column is added or removed later this still will remove the appropriate number of columns.

Dynamically filtering data to remove the first several rows of a document without hard coding a value

Another more complicated method would be to create a conditional column that checks for the existence of a specific column header value, returning a one or a zero. Then filling down to ensure that all rows below it are flagged as rows to keep. Then apply a basic filter after creating the key column.

How to Promote Headers in Power Query

To Promote Headers in Power Query , go to the “Home” Tab and select “Use First Rows as Headers” from the “Transform” section. This converts the values in the first row into column names.

The Power Query Use First Rows as Headers Button

Once promoted, the first row of data will become the column names.

Another important thing to note is that once columns are promoted, Power Query will automatically attempt to detect the data type for each column which can cause errors later on if the data type detection was inaccurate. Make sure to check data types for accuracy after promoting headers.

Modifying Data Types after promoting Headers in Power Query

Steps such as promoting headers and changing data types can be deleted by selecting the Applied Step on the right side of Power Query and clicking the X to the left of it.

Using the Table.Skip Power Query M Formula

Advanced Power Query users can use Power Query code to skip rows using the Table.Skip formula. This M function allows you to skip a specified number of rows using the syntax Table.Skip(Source, Number), where Source is your table, and Number is the number of rows to skip.

Here is how it looks using the Power Query Formula Editor.

The ability to modify Power Query M formulas can give users more control and ability to combine with other formulas to make skipping rows more dynamic or combined with parameters.

Working with Complex Promote Header Scenarios

In scenarios when you don’t want to promote all headers in a Power Query Dataset or the columns need to be renamed or concatenated with headers from another row, try Using Unpivot and Pivot to adjust Power Query Header Names.

When you pivot data in Power Query it will place all of the headers into a single column called Attributes and all of the values into a column called Values. The Attribute column can be adjusted with formulas or through a conditional column to combine and concatenate other values.

Once the transformations are made and a single column of header values exist, you can UnPivot the data.

To learn more about Pivot and Unpivot in Power Query check out our guide.

Promoting Headers with Non-Text Values in Power Query

Issues can arise when promoting headers in Power Query with non-text data like dates. This can result in losing the first row of data. To prevent this, ensure columns are formatted as text before promoting headers, then revert to the original format.

Skipping Bottom Rows in Power Query

To remove the bottom rows of a data set in Power Query, press the Remove Rows button on the Home Ribbon of Power Query, then enter the number of bottom rows to skip.

Removing the bottom rows of a dataset using the Remove Bottom Rows button in Power Query

Skipping bottom rows goes hand in hand when you’re removing top rows and promoting headers when importing pre-formatted documents.

Conclusion

To promote headers in Power Query, navigate to the Promote First Row to Headers button on the Home Tab of the Power Query Ribbon after importing data. The technique is often not used by itself, but in conjunction with skipping the first several rows of data and sometimes the last rows of data when working with pre-formatted documents.

Rows can either be skipped based on a specific number of rows, or you can use a filter to remove null rows at the top of a dataset. It’s best practice to make data transformations as dynamic as possible when data comes from human users as the data tends to change in formatting over time. When data comes directly from a source system it’s usually consistent enough to apply hard logic to Power Query Transformations.

Scroll to Top