// EOX Market Data API -> Excel table (Power Query).
// Reads the URL and your key from the named cells EOX_URL and EOX_KEY in EOX_API_Template.xlsx,
// sends the key as a header (it never appears in the URL), and returns the CSV as a table.
// Setup: Data > Get Data > From Other Sources > Blank Query > Advanced Editor > paste this > Done >
//        Close & Load To... > Table on the Data sheet. Then Data > Refresh All whenever you want new data.
let
    Url    = Excel.CurrentWorkbook(){[Name = "EOX_URL"]}[Content]{0}[Column1],
    Key    = Excel.CurrentWorkbook(){[Name = "EOX_KEY"]}[Content]{0}[Column1],
    Raw    = Web.Contents(Url, [Headers = [#"X-API-Key" = Key], ManualStatusHandling = {401, 403, 404, 422, 429}]),
    Status = Value.Metadata(Raw)[Response.Status],
    Result =
        if Status <> 200 then
            error Error.Record("EOX API " & Text.From(Status), Json.Document(Raw)[detail])
        else
            Table.PromoteHeaders(
                Csv.Document(Raw, [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]),
                [PromoteAllScalars = true])
in
    Result