I don't think you can create a dynamic number of queries but you can load all three tables into a list of tables like this:
let
Sheet1 = Csv.Document(Web.Contents("https://docs.google.com/spreadsheets/d/1WQ6n6FKM5ZjAnndcfY9xCoY-1IaTN5dAnckT0IXzz7E/export?format=csv")),
BlankRows = List.PositionOf(Sheet1[Column1], "", 2),
FirstBreak = BlankRows{0},
SecondBreak = BlankRows{1},
Table1 = TrimAndPromote(Table.Range(Sheet1, 0, FirstBreak)),
Table2 = TrimAndPromote(Table.Range(Sheet1, FirstBreak + 1, SecondBreak - FirstBreak - 1)),
Table3 = TrimAndPromote(Table.Range(Sheet1, SecondBreak + 1))
in
{Table1, Table2, Table3}
Where TrimAndPromote
is a separate custom function I've written as a separate query:
(T as table) as table =>
let
TrimBlankCols = List.Select(Table.ToColumns(T), each not List.ContainsAll({null,""}, _)),
PromoteHeaders = Table.PromoteHeaders(Table.FromColumns(TrimBlankCols))
in
PromoteHeaders
My assumption here is that you have exactly three tables to load but each table could have any (positive) number of rows or columns. A dynamic number of tables would get more complicated.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…