Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
Proud to be a Super User!
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.
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.
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.
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".
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |