Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I want to import all excel files from a folder and combine them. These files have a top row that needs to be set as column name. But there are some files that have extra first row before column name row, that needs to be removed. Power BI automatically detects the columns names for files without extra first rows, but not for the ones with extra row. How can I remove this extra row from problematic files?
Solved! Go to Solution.
Hi @Anonymous ,
If the row just in the sheet not in the table, you can connect to this table separately first.
Then connect to the folder to filter without the 'problem' excel file to combine them and remove uncessary columns.
Finally append the previous table and the combined table to get the final combined table:
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I'm assuming you have a column of tables (not binary), and that the tables without the extra top row already have the correct column names. This means the other tables will have columns named "Column1, Column2", etc. Before combining, add this step in the formula bar:
= Table.TransformColumns(NameOfPriorStep, {{"NameOfTableColumn", each if Table.ColumnNames(_){0} = "Column1" then Table.SkipFirstN(_, 1) else Table.DemoteHeaders(_)}})
Now all of your headers should be in the first row. You can now combine and PromoteHeaders.
--Nate
I'm assuming you have a column of tables (not binary), and that the tables without the extra top row already have the correct column names. This means the other tables will have columns named "Column1, Column2", etc. Before combining, add this step in the formula bar:
= Table.TransformColumns(NameOfPriorStep, {{"NameOfTableColumn", each if Table.ColumnNames(_){0} = "Column1" then Table.SkipFirstN(_, 1) else Table.DemoteHeaders(_)}})
Now all of your headers should be in the first row. You can now combine and PromoteHeaders.
--Nate
Thank you! It worked.
Hi @Anonymous ,
If the row just in the sheet not in the table, you can connect to this table separately first.
Then connect to the folder to filter without the 'problem' excel file to combine them and remove uncessary columns.
Finally append the previous table and the combined table to get the final combined table:
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you! This really helps in case where we know which particular tables have extra rows, such as in my case.
You can find some distinct condition to filter out that row.
But we can only practice our imagination without any sample of your data.
Sorry for not being descriptive.
The files with extra row have this structure:
Extra row | |||
Column header 1 | Header 2 | Header 3 | Header 4 |
1 | 2 | 3 | 4 |
1 | 2 | 3 | 4 |
While other don't have that first two rows and start from column header.
Also, I am importing all files from folder together, so I am not able to do any transformation before merging them. Is there any way to do that?
@Anonymous
What would be the logic to remove those extra 1st rows as p[er your data? If there anything common in those files, it could used to remove the rows.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi,
The files with extra row have this structure:
Extra row | |||
Column header 1 | Header 2 | Header 3 | Header 4 |
1 | 2 | 3 | 4 |
1 | 2 | 3 | 4 |
While other don't have that first two rows and start from column header.
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.