ApiSkills

Power Query (M) · 12 min

Power Query: Web.Contents & its options

The M function behind every web query — and the options record that controls it.

In Power Query (M), all web requests go through Web.Contents. It returns the raw response as binary; you wrap it in Json.Document to parse JSON. The magic is the second argument — an options record that controls the path, query, headers, body, and error handling.

let
    Source = Json.Document(
        Web.Contents(
            "https://api.ifsjaipur.cloud",
            [
                RelativePath = "playground/data/fpa/actuals",
                Query = [ shape = "flat", page = "1", page_size = "50" ]
            ]
        )
    )
in
    Source

The options record

OptionWhat it does
RelativePathPath appended to the base URL. Use this (not string concat) so the base stays static.
QueryA record of query-string params; Power Query URL-encodes them for you.
HeadersA record of request headers, e.g. [#"x-api-key" = "..."].
ContentA binary body — its presence turns the request into a POST.
ManualStatusHandlingA list of status codes to NOT raise an error on, e.g. {400, 404, 429}.
TimeoutA duration, e.g. #duration(0,0,0,30) for 30 seconds.
IsRetrySet true to bypass the cache when retrying.

Tip · Why RelativePath + Query matter: Power BI ties credentials and refresh to the STATIC base URL. If you build the full URL by string concatenation, the service cannot verify the data source and scheduled refresh breaks. Always keep the base literal and put the dynamic parts in RelativePath / Query.

A POST with a JSON body

let
    Body = Json.FromValue([ requests = { [method="GET", path="/a"], [method="POST", path="/b"] } ]),
    Source = Json.Document(
        Web.Contents(
            "https://api.ifsjaipur.cloud",
            [
                RelativePath = "playground/batch",
                Headers = [ #"Content-Type" = "application/json" ],
                Content = Body
            ]
        )
    )
in
    Source

Tip · ManualStatusHandling lets you inspect error responses instead of failing. Add ManualStatusHandling = {400, 429} and read Value.Metadata(response)[Response.Status] to branch on the code — perfect for the resilience endpoints.