Transcribe worksheets to table

As long as the worksheets are the same in the sense that the categories and values are in the same columns then you can do what you are looking for.
As a basic example if I start with data that is similar to this...

I can do the transformations on the nested tables prior to expanding them.
The end result could look something like this...

You can paste the following code into the advanced editor of a blank query to see the steps used.

let
    Source = 
    #table(
        {"Name", "Table"},
        {
            {"FirstFile", #table(null, {{null, null, null},{"indirect items", null, null}, {"Category", "Annual Amount", "Monthly Amount"},{"category1", 21000, 2100}, {"category2", 15000, 1500}, {null, null, null}})},
            {"SecondFile", #table(null, {{null, null, null},{"indirect items", null, null}, {"Category", "Annual Amount", "Monthly Amount"},{"category1", 36000, 1700}, {"category2", 18000, 1200}, {null, null, null}})},
            {"ThirdFile", #table(null, {{null, null, null},{"indirect items", null, null}, {"Category", "Annual Amount", "Monthly Amount"},{"category1", 31000, 4100}, {"category2", 16000, 1600}, {"category3", 2300, 230}, {null, null, null}})}
        }
    ),
    select_columns = Table.TransformColumns(Source, {{"Table", each Table.SelectColumns(_, {"Column1", "Column2"})}}),
    select_rows = Table.TransformColumns(select_columns, {{"Table", each Table.SelectRows(Table.Skip(_, 3), each [Column1] <> null and [Column2] <> null)}}),
    pivot_table = Table.TransformColumns(select_rows, {{"Table", each Table.Pivot(_, List.Distinct(_[Column1]), "Column1", "Column2")}}),
    get_column_names = Table.AddColumn(pivot_table, "Column Names", each Table.ColumnNames([Table])),
    distinct_column_names = List.Distinct(Table.ExpandListColumn(get_column_names, "Column Names")[Column Names]),
    expand_nested_tables = Table.ExpandTableColumn(pivot_table, "Table", distinct_column_names)
in
    expand_nested_tables