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. Extract the entire row data (a record). Covert that record into a list. Compare the list with the valid headers of the data i.e Date Category and Value. 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   .        Automate repetitive data cleaning tasks using Power Query A comprehensive course to learn Power Query to automate all your mundane and repetitive data cleaning tasks in Excel or in Power BI   DOWNLOAD THE COURSE OUTLINE    |    ENROLL IN THE COURSE