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

     

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