Remove Top Rows and Combine Data from Multiple Excel Files

While combining data from multiple excel files or from multiple sheets there can be a few top junk rows to remove before the data is combined. In this post, I’ll share 2 approaches to remove either equal or unequal number of junks rows.

 

Video – Remove Top Rows and Combine Data from Multiple Excel Files

 

Consider this Example – One Excel File with 3 Sheets

Each sheet contains three columns – Date, Category, and Value with unequal top junk rows. And yes, Sheet3 contains no junk rows!

I load this in Power Query. Note that as of now none of the tables are expanded.

 

Skipping equal number of top junk rows using Table.Skip

Let’s assume for a moment if there were 2 junk rows in each sheet. I can remove them by creating a custom column in Power Query.

= Table.PromoteHeaders(Table.Skip([Data],2))

• This creates a table where the top two rows are skipped.
• And the headers are promoted.

Here is how the cleaned table would look like.

Once you have all the tables cleaned, you can expand and combine the data from all the 3 sheets. But our problem is that we do not have an equal number of junk top rows. Let’s refine the logic further.

 

Skipping an unequal number of top junk rows

Here is the logic that I am going to follow to clean each table.

1. Extract the entire row data (a record).
2. Covert that record into a list.
3. Compare the list with the valid headers of the data i.e Date Category and Value.
4. Stop removing top rows where headers match the valid headers.

To build all this logic I am going to create a custom column with the following M Code:

= Table.PromoteHeaders(
       Table.Skip(      
       [Data],        
       each Record.ToList(_) <> {'Date', 'Category', 'Value'} 
   )
)

Below is the preview of the cleaned tables.

The Custom Column (with all cleaned tables) now needs to be expanded.

I finally get to combine all the data without any junk rows.

 

 

More on Power Query

Quick VLOOKUP in Power Query

Dynamic Unpivoting in Power Query

Unpivot Cross Tabulated Data with Multiple Headers and Columns

Dynamic Filter by a Range of Values in Power Query

Dynamic Data Types in Power Query