How can I combine a single query of multiple sub-tables (with repeating headers, etc) into a single table

Question

I have a query (weather data) that results in multiple sub-tables. For example, the initial raw query looks like this:

Column1	Column2	Columns3-12
Boston	Hourly Forecast Made Dec 6 2023 1134 UTC
LocalTime	Temp	...
12/6/2023 12:00:00 AM	32	...
12/6/2023 1:00:00 AM	30.92	...
...	...	...
12/20/2023 11:00:00 PM	38.48	...
NYC	Hourly Forecast Made Dec 6 2023 1134 UTC
LocalTime	Temp	...
12/6/2023 12:00:00 AM	24.98	...
...	...	...

So each "sub-table" consists of two header rows: the first with two columns including the city and the forecast date, and the second containing the column headers (12 of them, in this case).

Then we have 360 rows of data (not sure if it's always 360), followed by a blank row, and then that pattern repeats for the next city (in my example above, "NYC").

Simply removing the first row, promoting the headers from the second row and then filtering all other "non-forecast" values is easy. What I would really like to do is figure out a way to add the city name to a new column, so that I get this end result:

City	LocalTime	Temp	...
Boston	12/6/2023 12:00:00 AM	32	...
Boston	12/6/2023 1:00:00 AM	30.92	...
Boston	...	...	...
Boston	12/20/2023 11:00:00 PM	38.48	...
NYC	12/6/2023 12:00:00 AM	24.98	...
NYC	...	...	...

I have tried adding index columns and referencing previous rows and all that without much success. My temporary solution will be to just use formulas in Excel itself, but I'm hoping to have a pure Powerquery solution, if only for my own education

Answer 1

When you're at this stage:

Add a column:

try if Text.StartsWith([Column2], "Hourly Forecast") then [Column1] else null otherwise null

Then fill down

Answer 2

Same approach as the other answer, with a few added bits to help. Like renaming the last column dynamically to City.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsovLsnPU9JR8sgvLcqpVHDLL0pNTiwuUfBNTElVcElNVjBTMDIwMlYwNDQ2UQgNcQYqVYrViVbyyU9OzAnJzE0FCoSk5hYAKT09PbCUoZG+mT5Ek5GVgQEQKTj6AuWNjbArQlFjoGeJpszIAKoOpjAArNBCz8QCSaFfpDN9fGFkomcJtzgWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, #"Columns3-12" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Columns3-12", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "CIty", each if Text.StartsWith([Column2], "Hourly") then [Column1] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"CIty"}),
    #"Removed Top Rows" = Table.Skip(#"Filled Down",1),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"LocalTime", type text}, {"Temp", type text}, {"...", type text}, {"Boston", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each (Text.StartsWith([Temp], "Hourly") = false and [Temp] <> "Temp" and [Temp] <> null and [Temp] <> "")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{ List.Last(Table.ColumnNames(#"Filtered Rows")), "City"}})
in
    #"Renamed Columns"