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

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

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:

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

>Solution :

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