// WeatherLabs — EVERY variable for one location, into Excel (Power Query M)
//
// Excel: Data → Get Data → From Other Sources → Blank Query → Advanced Editor,
// paste this, set your coordinates + key, Close & Load. You get one wide table:
// a valid_time column plus one column per surface variable (t2m, u10, v10,
// gust, prate, msl, cape, …) — 209 forecast rows, refreshable with one click.
//
// Docs: https://weatherlabs.io/pages/docs.html
let
    ApiBase = "https://api.weatherlabs.io",
    ApiKey  = "wl_live_REPLACE_ME",
    Lat = 51.5074,          // ← your site
    Lon = -0.1278,

    Auth = [#"Authorization" = "Bearer " & ApiKey],

    // ---- discover what the current cycle carries --------------------------
    VarsResp = Json.Document(Web.Contents(ApiBase, [
        RelativePath = "v1/gfs/latest/vars",
        Headers = Auth
    ])),
    SurfaceVars = List.Transform(
        List.Select(VarsResp, each [kind] = "surface"),
        each [name]
    ),

    // ---- one /forecast call per variable, joined on valid_time ------------
    // (vars= takes a comma-separated list of up to 8 — batch if you like; the
    // per-var calls here keep the M simple)
    GetSeries = (v as text) as table =>
        let
            Resp = Json.Document(Web.Contents(ApiBase, [
                RelativePath = "v1/gfs/latest/forecast",
                Query = [
                    lat = Number.ToText(Lat),
                    lon = Number.ToText(Lon),
                    #"vars" = v,
                    interp = "bilinear"
                ],
                Headers = Auth
            ]))
        in
            Table.FromColumns(
                {Resp[valid_times], Record.Field(Resp[vars], v)[values]},
                {"valid_time", v}
            ),

    Tables = List.Transform(SurfaceVars, GetSeries),
    Joined = List.Accumulate(
        List.Skip(Tables, 1),
        Tables{0},
        (acc, t) => Table.Join(acc, "valid_time", t, "valid_time")
    ),
    Sorted = Table.Sort(Joined, {{"valid_time", Order.Ascending}}),
    Typed = Table.TransformColumnTypes(Sorted, {{"valid_time", type datetimezone}})
in
    Typed

// Notes:
//  * Units are SI straight from the model (see …/vars): Kelvin, Pa, m/s,
//    kg/m²/s. Add Excel columns like  = [t2m] - 273.15  for °C.
//  * Quota: batching vars (vars = "t2m,u10,v10,…", up to 8) shares ONE 32 KiB
//    billing floor per call — ~7 variables in a single call costs one floor.
//  * Want only 5 days hourly? Add  steps = "0-120"  to each Query record.
