Build a Live European Weather Forecast in Excel with Open-Meteo (part 1)

MirVel
2 days ago
4 min read

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

Weather data can support logistics, retail planning, field operations, travel, events, and staffing. Manual copying creates stale data, inconsistent sources, and no audit trail. Power Query replaces that process with a repeatable connection. Here, “live” means the latest available API response is retrieved whenever the workbook is refreshed; it is not a continuously streaming feed.

Solution Architecture

Use four layers: a Cities table for configuration, a reusable function for the API request, a reporting query for transformations, and an Excel dashboard for analysis. Separating these layers makes the workbook easier to review, extend, and maintain.

Open-Meteo homepage with blue sky and clouds, weather icon, Free Weather API text, and Features and Try the API buttons. — https://open-meteo.com/

Step-by-Step Solution

Step 1: Create the City Table

Create a worksheet named Cities with City, Country, Latitude, Longitude, and Active. Example locations are Berlin (52.5200, 13.4050), Paris (48.8566, 2.3522), Rome (41.9028, 12.4964), Vienna (48.2082, 16.3738), Warsaw (52.2297, 21.0122), Oslo (59.9139, 10.7522), Dublin (53.3498, -6.2603), and Athens (37.9838, 23.7275). Convert the range to an Excel table and name it tblCities. The Active field lets users control locations without editing M code.

Step 2: Create the Open-Meteo Function

Choose Data → Get Data → From Other Sources → Blank Query, open Advanced Editor, paste the following code, and rename the query fxOpenMeteoForecast.

(latitude as number, longitude as number) as table =>
let
    BaseUrl = "https://api.open-meteo.com",
    RefreshUTC = DateTimeZone.UtcNow(),
    Response = Json.Document(
        Web.Contents(
            BaseUrl,
            [
                RelativePath = "v1/forecast",
                Query = [
                    latitude = Number.ToText(latitude, "0.####", "en-US"),
                    longitude = Number.ToText(longitude, "0.####", "en-US"),
                    current = "temperature_2m,relative_humidity_2m,apparent_temperature,weather_code,wind_speed_10m",
                    daily = "weather_code,temperature_2m_max,temperature_2m_min,precipitation_sum,precipitation_probability_max,wind_speed_10m_max",
                    temperature_unit = "celsius",
                    wind_speed_unit = "kmh",
                    precipitation_unit = "mm",
                    timezone = "auto",
                    forecast_days = "7"
                ],
                Timeout = #duration(0,0,0,30)
            ]
        )
    ),
    Current = Response[current],
    Daily = Response[daily],
    Data = Table.FromColumns(
        {
            Daily[time], Daily[weather_code],
            Daily[temperature_2m_max], Daily[temperature_2m_min],
            Daily[precipitation_sum],
            Daily[precipitation_probability_max],
            Daily[wind_speed_10m_max]
        },
        {
            "ForecastDate","WeatherCode","MaximumTemperatureC",
            "MinimumTemperatureC","PrecipitationSumMM",
            "PrecipitationProbabilityPct","MaximumWindSpeedKMH"
        }
    ),
    Typed = Table.TransformColumnTypes(
        Data,
        {
            {"ForecastDate", type date},
            {"WeatherCode", Int64.Type},
            {"MaximumTemperatureC", type number},
            {"MinimumTemperatureC", type number},
            {"PrecipitationSumMM", type number},
            {"PrecipitationProbabilityPct", Int64.Type},
            {"MaximumWindSpeedKMH", type number}
        },
        "en-US"
    ),
    AddCurrent = Table.AddColumn(Typed, "CurrentTemperatureC", each try Current[temperature_2m] otherwise null, type number),
    AddFeelsLike = Table.AddColumn(AddCurrent, "CurrentFeelsLikeC", each try Current[apparent_temperature] otherwise null, type number),
    AddHumidity = Table.AddColumn(AddFeelsLike, "CurrentHumidityPct", each try Current[relative_humidity_2m] otherwise null, Int64.Type),
    AddWind = Table.AddColumn(AddHumidity, "CurrentWindSpeedKMH", each try Current[wind_speed_10m] otherwise null, type number),
    AddRefresh = Table.AddColumn(AddWind, "RefreshTimestampUTC", each RefreshUTC, type datetimezone)
in
    AddRefresh

The function accepts coordinates, calls the forecast endpoint, converts JSON into a table, applies explicit types, and adds current conditions plus a UTC refresh timestamp. RelativePath and the Query record are preferable to concatenating one long URL. When prompted for credentials, select Anonymous for the Open-Meteo domain.

Step 3: Add Readable Weather Descriptions

Create fxWeatherDescription to translate numerical weather codes. For example: 0 = Clear sky; 1–3 = Mainly clear, partly cloudy or overcast; 45 and 48 = Fog; 51–57 = Drizzle; 61–67 = Rain; 71–77 and 85–86 = Snow; 80–82 = Rain showers; and 95–99 = Thunderstorm. Keep the original code for traceability.

Step 4: Run the Function for Every Active City

let
    Source = Excel.CurrentWorkbook(){[Name="tblCities"]}[Content],
    Typed = Table.TransformColumnTypes(
        Source,
        {{"City", type text},{"Country", type text},
         {"Latitude", type number},{"Longitude", type number},
         {"Active", type logical}},
        "en-US"
    ),
    Active = Table.SelectRows(Typed, each [Active] = true),
    AddedForecast = Table.AddColumn(
        Active,
        "Forecast",
        each fxOpenMeteoForecast([Latitude],[Longitude]),
        type table
    ),
    Expanded = Table.ExpandTableColumn(
        AddedForecast,
        "Forecast",
        {"ForecastDate","WeatherCode","MaximumTemperatureC",
         "MinimumTemperatureC","PrecipitationSumMM",
         "PrecipitationProbabilityPct","MaximumWindSpeedKMH",
         "CurrentTemperatureC","CurrentFeelsLikeC",
         "CurrentHumidityPct","CurrentWindSpeedKMH",
         "RefreshTimestampUTC"}
    ),
    Description = Table.AddColumn(
        Expanded,
        "WeatherDescription",
        each fxWeatherDescription([WeatherCode]),
        type text
    )
in
    Description

Rename the query rptWeatherForecast and load it to a WeatherData worksheet. Eight cities with seven forecast days produce 56 rows.

Step 5: Configure Refresh

Use Data → Refresh All to retrieve the newest forecast. In Queries & Connections, open Properties and optionally enable Refresh data when opening the file or a 60–180 minute refresh interval while Excel is open. A closed desktop workbook requires a separate automation or hosted refresh process.

Code Governance and Quality Controls

Keep configuration outside the function. Use tblCities and optional parameters for forecast days and units. Apply clear names such as fx for functions, stg for staging queries, rpt for reporting outputs, and p for parameters. Request Celsius, kilometres per hour, and millimetres explicitly, and assign Power Query types deliberately.

Retain RefreshTimestampUTC and display it on the dashboard. Use try … otherwise null for optional fields, and consider wrapping each city request in try so one bad coordinate does not stop all locations. Add a Documentation sheet containing the workbook owner, API endpoint, query names, units, refresh process, last review date, and known limitations.

Build the Excel Weather Dashboard

Create KPI cards for current temperature, feels-like temperature, humidity, wind speed, today’s high and low, precipitation probability, and last refresh time. Add a city selector, a seven-day line chart for maximum and minimum temperatures, a column chart for precipitation, and a detailed forecast table.

Current values repeat across the seven daily rows for a city, so do not sum them. Use MAX, MIN, or a separate current-weather query. Example KPI formula for the city selected in B2:

=MAXIFS(tblWeatherForecast[CurrentTemperatureC],tblWeatherForecast[City],$B$2)

Common Mistakes

Do not send city names to the forecast endpoint; it requires coordinates. Do not let German decimal commas enter the URL—Number.ToText with en-US produces API-safe coordinates. Do not treat refreshed forecasts as a historical archive: forecast values can change. To analyse revisions, append snapshots with their refresh timestamps. Finally, keep the WeatherData sheet available so users can validate calculations behind the dashboard.

Recommended Workbook Structure

Use Dashboard for KPIs and charts, WeatherData for query output, Cities for configuration, Parameters for optional settings, and Documentation for governance. This structure remains understandable when new locations, weather fields, or reports are added.

Final Thoughts

Excel can be a practical front end for API-based reporting. Open-Meteo supplies structured weather data, Power Query handles the connection and transformations, and Excel provides formulas, PivotTables, charts, slicers, and familiar reporting tools. The key is to separate configuration, connection logic, transformation, and reporting so the solution stays refreshable, auditable, and easy to extend.

POSTS