Transform more than 100 Ranges in same worksheet in Tables with Power Query

If you ask to import them in PQ as separate tables named after corresponding Description, IMHO only way to do it is via VBA in Excell. E.g. Recognize, create and name ranges/tables, then import in PQ.

Otherwise if you want extra column with table names paste this in blank query.

Enter your FilePath and FileName inbetween the quotes at the beginning.

let
    Source = Excel.Workbook(File.Contents("FilePath.xlsx"), null, true),
    Navigation = Source{[Item="FileName(without extension)",Kind="Sheet"]}[Data],
    #"Removed Columns" = Table.RemoveColumns(Navigation,{"Column1"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each if Text.StartsWith([Column2],"Table ") then [Column2] else null),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Added Custom", {"Custom"}),
    #"Filled Down" = Table.FillDown(#"Removed Errors",{"Custom"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Filled Down",List.LastN(Table.ColumnNames(#"Filled Down"),1)&List.RemoveLastN(Table.ColumnNames(#"Filled Down"),1)),
    #"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each ([Custom] <> null)),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each not Text.StartsWith([Column2], "Table ")),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows1", [PromoteAllScalars=true]),
    #"Filtered Rows2" = Table.SelectRows(#"Promoted Headers", each ([Country] <> "Country")),
    FINAL = Table.RenameColumns(#"Filtered Rows2",{{Table.ColumnNames(#"Filtered Rows2"){0}, "Table"}})
in
    FINAL