To remove the first row from a column in Power Query, you must use the
Remove Top Rows function on the entire table before expanding the column. This approach ensures that the header row is excluded from the data load. If your data is already in a list format within a single cell, use
List.Skip to filter out the initial element. These methods guarantee clean data without retaining unwanted headers or duplicate entries in your final dataset.
Understanding the Data Structure
The solution depends entirely on how your data is structured within the Power Query Editor. Most users encounter this issue when importing data where the first row contains column headers that need to be promoted, but those headers also appear as data in subsequent steps, or when dealing with nested tables. It is crucial to distinguish between removing a row from the main table view versus manipulating a specific list value inside a cell. The most common scenario involves cleaning up imported data where the header row was not correctly handled during the initial import phase.
Method 1: Removing the Top Row from the Table
This is the standard and most robust method for cleaning up imported datasets. When you load data into Power Query, the first row often contains headers. If you promote these headers, they become column names. However, if you do not promote them, or if you have a scenario where the header text persists as the first data row, you can remove it using the ribbon interface.
Navigate to the
Home tab in the Power Query Editor window. Look for the
Reduce Rows group on the far left of the toolbar. Click on the dropdown arrow next to
Keep Top Rows or directly select
Remove Top Rows. A dialog box will appear asking for the number of rows to skip. Enter
1 to remove the very first row of the current table view. This action shifts all subsequent rows up by one position. After clicking OK, observe the preview pane to ensure that the desired data remains intact and the unwanted header or blank row is gone. This method operates on the table level, meaning it affects every column simultaneously, which is usually what you want when cleaning source data.
For more information on handling complex data transformations, see
How to Manipulate Hundreds of Thousands of Rows of Data in Excel Without Freezing.
Method 2: Using List.Skip for Nested Lists
Sometimes, the "first row" problem exists within a single cell that contains a list or an array of values. For example, if you have expanded a table column and the result is a list of values, and you only want to keep the items after the first one, you cannot use the ribbon menu. Instead, you must use M code.
Right-click the column header containing the list and select
Custom Column. In the formula bar, you will define a new column using the
List.Skip function. The syntax is straightforward:
List.Skip([YourColumnName], 1). The first argument specifies the list, and the second argument specifies how many elements to skip from the beginning. By entering
1, you discard the first item in the list. You can then replace the original column with this new custom column or delete the old one. This technique is essential when working with hierarchical data or JSON responses where arrays are stored within cells.
If you are struggling with queries that fail to update after source changes, check
Excel Query Not Removing Row Once Deleted from Source Table with Refresh for troubleshooting tips.
Verifying Your Changes
After applying either method, it is vital to verify that the transformation has been applied correctly. Check the bottom right corner of the Power Query Editor for the row count; it should decrease by one if you used Method 1. For Method 2, inspect a few sample rows to ensure the list structure remains valid and no errors have been introduced. Always click
Close & Apply to load the cleaned data back into Excel. This step commits your changes to the worksheet. If you notice that the first row reappears upon refreshing, review your query steps to ensure no previous step is re-introducing the header data.
Common Pitfalls
A frequent mistake is attempting to delete the first row after the data has already been loaded into Excel. Power Query transformations happen in the editor, not in the spreadsheet itself. Another error is confusing the "First Row as Headers" option with actual data removal. Promoting headers converts the first row into column names; it does not delete the row from the underlying data buffer unless you subsequently remove it. Ensure you understand whether you are dealing with a table row or a list element before choosing your strategy.
Frequently Asked Questions
Does Remove Top Rows delete the header?
No, it removes the physical row at index zero. If you have already promoted headers, this removes the first data record. If headers are still plain text, this removes the header row itself, leaving the next row as the new top entry.
Can I remove the first row using the ribbon for lists?
No, the ribbon options like Remove Top Rows apply to the entire table structure. To manipulate individual list elements within a cell, you must use the M language function List.Skip in a Custom Column.
What happens if my table has fewer than two rows?
If you try to remove the top row from a table with only one row, the result will be an empty table. Be cautious when applying this to dynamic sources that might occasionally return minimal datasets, as this could break downstream calculations.
How do I undo a removal in Power Query?
You can simply click the red X next to the step in the Applied Steps pane on the right side. This deletes the step and reverts the data to its state prior to the removal, allowing you to adjust the number of rows skipped.
Is there a way to remove the last row instead?
Power Query does not have a direct "Remove Bottom Rows" button. However, you can reverse the order of rows using the Sort feature, apply Remove Top Rows, and then sort again. Alternatively, use advanced M code to filter based on index positions.