Forum Discussion

luigi86's avatar
luigi86
New Member
2 years ago
Solved

Combining excel sheet with different first row

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 

 

Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    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

     

2 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    2 years ago

    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

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    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