// WeatherLabs — daily forecasts for a table of sites (Power BI / Power Query M)
//
// Paste into Power BI Desktop: Home → Get Data → Blank Query → Advanced Editor.
// Produces one row per site per forecast day: Date, Min/Max/Mean °C — ready for
// a map visual (Lat/Lon) + line/column charts sliced by site.
//
// Auth: set ApiKey below, or better, make it a Power BI parameter. The key is
// sent ONLY as an Authorization header (never in the URL). Using Web.Contents
// with RelativePath/Query keeps scheduled refresh happy (static host).
//
// Docs: https://weatherlabs.io/pages/docs.html
let
    ApiBase = "https://api.weatherlabs.io",
    ApiKey  = "wl_live_REPLACE_ME",

    // ---- your sites: edit/replace with a reference to any table ----------
    Sites = #table(
        {"Site", "Lat", "Lon"},
        {
            {"London HQ",      51.5074, -0.1278},
            {"Manchester DC",  53.4808, -2.2426},
            {"Edinburgh Depot",55.9533, -3.1883}
        }
    ),

    // ---- one API call per site: daily t2m aggregates ---------------------
    GetDaily = (lat as number, lon as number) as table =>
        let
            Resp = Json.Document(Web.Contents(ApiBase, [
                RelativePath = "v1/gfs/latest/forecast",
                Query = [
                    lat = Number.ToText(lat),
                    lon = Number.ToText(lon),
                    #"vars" = "t2m",
                    granularity = "daily"
                ],
                Headers = [#"Authorization" = "Bearer " & ApiKey]
            ])),
            Days = Table.FromRecords(Resp[vars][t2m][daily]),
            // Kelvin → Celsius for the three stat columns
            Celsius = Table.TransformColumns(Days, {
                {"min",  each _ - 273.15, type number},
                {"max",  each _ - 273.15, type number},
                {"mean", each _ - 273.15, type number}
            })
        in
            Celsius,

    WithForecast = Table.AddColumn(Sites, "Forecast", each GetDaily([Lat], [Lon])),
    Expanded = Table.ExpandTableColumn(
        WithForecast, "Forecast",
        {"date", "min", "max", "mean"},
        {"Date", "Min °C", "Max °C", "Mean °C"}
    ),
    Typed = Table.TransformColumnTypes(Expanded, {
        {"Date", type date},
        {"Lat", type number}, {"Lon", type number},
        {"Min °C", type number}, {"Max °C", type number}, {"Mean °C", type number}
    })
in
    Typed

// Variations:
//  * gusts instead of temperature: #"vars" = "gust" (m/s — drop the −273.15!)
//  * pin a cycle for a reproducible report: RelativePath =
//      "v1/gfs/2026060512/forecast" (immutable, cache-friendly)
//  * hourly series instead of daily: drop the granularity field, then
//      Table.FromColumns({Resp[valid_times], Resp[vars][t2m][values]}, {"Time","Value"})
