Load multiple files from folder without duplicating headers

Question

I have used folder data source in Power BI the files are excel files with same structure

each one of these files have columns names are first when I load the data I get the column name as row although i clicked in the "Use First row as header" button

how can I remove first row from all files.

I may end with 100s of files in that folder, so I cant remove them one by one

Answer 1

I had similar problem to solve. I found answer here: https://powerbi.tips/2016/08/load-multiple-excel-xlsx-files/

The logic is:

1. When you get data from Excel file, you have column Date with value Table for each sheet.

2. Refer to Data column, not expanding it. Add new column:

   = Table.AddColumn(#"Previous Step", "TablesWithHeaders", each Table.PromoteHeaders([Data], [PromoteAllScalars=true]))

3. Expand column TablesWithHeaders.