Combine files with extra top row before column name

[Anonymous]

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?

[v-yingjl · Accepted 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.

remove the name column

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.

[Anonymous · Accepted solution]

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

[Anonymous]

Thank you! It worked.

[v-yingjl · Accepted 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.

remove the name column

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.

[Anonymous]

Thank you! This really helps in case where we know which particular tables have extra rows, such as in my case.

[Jakinta]

You can find some distinct condition to filter out that row.

But we can only practice our imagination without any sample of your data. 

[Anonymous]

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?

[Fowmy]

@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.

[Anonymous]

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.