Splitting a file with get and transform

Hi Huge,

Yes, the idea is to define where is the row with your second header within the source table. You may add Index column, select the row with second header, define it's position based on Index and skip all rows before it to have second part of the table.

Then duplicate the query and do exactly the same but keep only first rows before second header.

Both queries to land as tables into separate sheets.

Like in this script

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddIndex = Table.AddIndexColumn(Source, "Index", 0, 1),

    // Select the row with second header
    SecondHeader = Table.SelectRows(AddIndex, each ([Column1] = "x")),
    // and find it's position
    RowToSplit = SecondHeader{0}[Index],

    // Based on it skip first part
    SecondPart = Table.Skip(Source,RowToSplit),

    // and cosmetic
    PromotedHeaders = Table.PromoteHeaders(SecondPart, [PromoteAllScalars=true]),
    ToNumber = Table.TransformColumnTypes(PromotedHeaders,{{"x", Int64.Type}, {"y", Int64.Type}})
in
    ToNumber

and in attached file.

Details depends on how your initial data is structured.