Build a Live European Weather Forecast in Excel (+ Power BI) with Open-Meteo (part 2)
- MirVel
- 48 minutes ago
- 2 min read
As mentioned in Part 1, most weather reports in Excel are copied manually and become outdated quickly. With Power Query and the Open-Meteo API, Excel can retrieve current conditions and a seven-day forecast for multiple European cities, refresh the information on demand, and turn it into a practical dashboard.
This guide covers the complete workflow: city configuration, a reusable API function, governed M code, refresh controls, validation, and reporting.
Why This Matters
This post shows how Excel can be turned into a live weather reporting tool using Power Query and the Open-Meteo API. The solution pulls hourly and daily forecast data directly into Excel, structures it into clean tables, and uses it to build an interactive dashboard with temperature, humidity, wind, rain probability, daylight, and forecast trends.
The result is more than a simple weather table: it demonstrates how Excel can connect to APIs, refresh external data, and present it in a professional, business-ready dashboard format.
These are live data from Open-Meteo, and API query looks something like this: let
RawResponse =
Web.Contents(
"https://api.open-meteo.com",
[
RelativePath = "v1/forecast",
....
The whole Query code can be downloaded below:
Simply define which cities you would like to see, determine their names into lists inside query and reload it.
The data that comes from Open Meteo looks like this:
There are many possibilities how to build a dashboard, no matter if it is Excel or Power BI (or any other tool actually), the data is stable mit minor tweaks to get desired outputs.
The biggest value of the post is showing that Excel can handle live API data, reporting logic, and visual storytelling in one workflow.
This dashboard for practice will be provided for a download too:
The dark Power BI edition takes the same idea one step further. It turns the weather data into a more polished, app-like reporting experience with modern visuals, compact KPI cards, city comparison, daylight tracking, and a stronger user interface. This makes the solution feel less like a spreadsheet and more like a professional weather analytics dashboard.
The key lesson is simple: with Power Query as the engine, Excel and Power BI can both become powerful tools for working with live external data. Whether used for learning APIs, building dashboards, or creating business-style reporting templates, this approach is flexible, practical, and highly reusable.
Comments