Conditionally Remove Top Rows from Table when Combining Multiple Files

[jwesle]

Hello,

I am attempting to combine several excel files from a folder, but they are not all in the same format. The only difference in the files is that some contain two rows at the top before the Column Headers, while the other files do not contain these rows. Examples are below:

Excel File 1:

Column1	Column2	Column3	Column4
No Filters applied	null	null	null
null	null	null	null
Location	Agent	Date	Report
East	John Doe	11/24/2019	A
West	Jane Doe	11/19/2019	A

Excel File 2:

Column1	Column2	Column3	Column4
Location	Agent	Date	Report
East	Jack Doe	11/22/2019	B
Central	Janet Doe	11/27/2019	A

Is there a way to conditionally remove the top two rows from the Tables prior to promoting the headers and combining the files? As an example, I have been searching for a way to remove the rows if Column 3 contains a null value, as an example, but have been unsuccessful.

Thanks.

[Nathaniel_C]

Hi @jwesle ,
In Power Query, on the Home tab, click on Remove Rows, Remove Top Rows, enter number of rows. All part of cleaning your data.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[jwesle]

Thanks for the responses. I'm looking for a way to conditionally remove the rows prior to merging the tables, so I don't think removing the Top 2 rows as you describe is a valid approach. Also, not all of the tables can have these top 2 rows removed, as sometimes there is data in those rows.

Excel File 1: No rows to be excluded

Excel File 2: Two rows that need to be excluded

[jdbuchanan71]

@jwesle 

I am going to assume that column 4 (Sales Order) has a value on every row you want to keep.  For all of your files you can apply the step that removes NULLs from column 4.  After that you promote the first row to header.  It should work for all your files wheter or not they have the extra row.  You just need to apply the filter before you promote the header.

[jwesle]

How do I apply the step that removes the nulls from column 4 prior to expanding the tables? There are roughly 400 excel files that will be combined in this dataset.

[jdbuchanan71]

You can't apply a step to the table before expanding it so you would just have to apply it after and also remove any row where Column 4 = "Order Number".

[jdbuchanan71]

@jwesle 

You can apply a filter to column3 to remove NULL.  If there are no nulls in column 3 (your second example) it will will not remove any rows.