Combining excel sheet with different first row

[luigi86]

Hello, I receive from time to time an excel  report that consist in a sheet with a tittle in cell A1 and then a table ( not really a table) with common headers from row 6 

My idea is to shave all this excel files in a folder so then I can combine them all together in Power Query . deleting top 5 rows. 

The problem is the tittle is always different and Power Query reads the first row as header. 

Is there is a way to combine them without first row as a header , so I then can delete top rows and make a combined table 

thanks 

[dufoq3 · Accepted solution]

Hi @luigi86, you can also remove top columns dynamically

Before

After

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkmtKFFITEpW0lECoVidaCXsTEMjYyReiGOwN5AdHOIYFAKkXUqLEksy8/MUNFISK4s1wUpKDIESllYGBkDKCCJiBGQaGkCEjJGEDCFCpkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Some Title" = _t, Column1 = _t, Column2 = _t]),
    RemovedTopRowsDynamic = Table.Skip(Source, each Record.Field(_, Table.ColumnNames(Source){0}) <> "TASK")
in
    RemovedTopRowsDynamic

[BA_Pete]

Hi @luigi86 ,

Bring in your files to combine them ignoring the preview that you've shown here. Once you've brought in your files and gone into Load & Transform, find the 'Transform Sample File' query in the Helper Queries that Power Query sets up for you. Within that query, you should see a step called 'Promoted Headers' just under the Source/Navigation steps. Delete this step and the files should now come through as you need.

Pete