Web.Contents Function (Power Query M)

If you want to pull data from a website or web API into Power Query, Web.Contents is the function that does the actual download. It hands you back the raw response, which you then parse into something usable.

In this article, I’ll show you how Web.Contents works, what its options record can do, and the gotchas to watch for.

Syntax of Web.Contents Function

Web.Contents(url as text, optional options as nullable record) as binary

where

  • url (required, text). The web address to request. This is the base URL, and credentials get tied to it (more on that below).
  • options (optional, nullable record). A record that controls the request: the relative path, query parameters, headers, POST content, status handling, and more. The keys are listed in The options record section below.

Returns: the downloaded content as a binary value, which you usually wrap in Json.Document, Csv.Document, or similar to parse it.

In plain terms, you give it a URL, it fetches the response, and you parse that response into a value, table, or record. If you are new to the tool, the Getting Started with Power Query guide covers the basics first.

Example 1: Fetch and parse a JSON endpoint

Request a single record from a JSON test API and read one field from it.

let
    Source = Json.Document(Web.Contents("https://jsonplaceholder.typicode.com/todos/1")),
    Title = Source[title]
in
    Title

Result: delectus aut autem

Web.Contents downloads the response and Json.Document turns it into a record, so Source[title] pulls out the title field.

Example 2: RelativePath keeps the base URL static

Split the address into a base URL plus a RelativePath so the base stays fixed.

let
    Source = Json.Document(
        Web.Contents(
            "https://jsonplaceholder.typicode.com",
            [RelativePath="users/1"]
        )
    ),
    Email = Source[email]
in
    Email

Result: Sincere@april.biz

The full request still hits /users/1, but the base URL is now a constant. That is what keeps refresh and credentials stable.

Example 3: Query passes parameters as a record

Use the Query key to add URL query parameters without building the string yourself.

let
    Source = Json.Document(
        Web.Contents(
            "https://jsonplaceholder.typicode.com",
            [
                RelativePath="comments",
                Query=[postId="1"]
            ]
        )
    ),
    AsTable = Table.FromList(Source,Splitter.SplitByNothing(),null,null,ExtraValues.Error),
    RowCount = Table.RowCount(AsTable)
in
    RowCount

Result: 5 (the /comments endpoint returns 5 comments for postId=1)

The Query record becomes ?postId=1 on the request. Power Query handles the encoding, so you avoid manual string building.

Example 4: Headers sends an Accept header

Pass request headers through the Headers key, for example to ask for a specific media type.

let
    Source = Json.Document(
        Web.Contents(
            "https://api.github.com",
            [
                RelativePath="repos/microsoft/PowerBI-visuals",
                Headers=[Accept="application/vnd.github+json",#"User-Agent"="PowerQuery"]
            ]
        )
    ),
    FullName = Source[full_name]
in
    FullName

Result: microsoft/PowerBI-visuals

Each field in the Headers record becomes a request header. Note the #"User-Agent" syntax, needed because the header name has a hyphen.

Example 5: ManualStatusHandling stops a 404 from failing the query

By default a non-2xx status throws an error. List the status codes you want to handle yourself in ManualStatusHandling.

let
    Response = Web.Contents(
        "https://jsonplaceholder.typicode.com",
        [
            RelativePath="todos/99999999",
            ManualStatusHandling={404}
        ]
    ),
    Status = Value.Metadata(Response)[Response.Status],
    Result = if Status=404 then "Not found - handled gracefully" else Json.Document(Response)
in
    Result

Result: Not found - handled gracefully (the 404 is caught instead of throwing a DataSource.Error)

With 404 in ManualStatusHandling, the response comes back instead of erroring. You read the status from Value.Metadata and branch on it.

Example 6: Base URL plus RelativePath plus Query for a clean parameterized call

Combine all three pieces, a static base URL, a RelativePath, and a Query record, for a fully parameterized request.

let
    BaseUrl = "https://jsonplaceholder.typicode.com",
    Source = Json.Document(
        Web.Contents(
            BaseUrl,
            [
                RelativePath="posts",
                Query=[userId="1",_limit="3"]
            ]
        )
    ),
    AsTable = Table.FromRecords(Source),
    RowCount = Table.RowCount(AsTable)
in
    RowCount

Result: 3 (filters posts to userId=1 and caps the result at 3 rows via the _limit query param)

The base URL stays constant while RelativePath and Query vary. This is the pattern to copy for any API call you want to refresh reliably.

The options record

These are the most useful keys you can put in the options record:

  • RelativePath (text). Appended to the base URL to form the full path, while the base stays constant for credentials and refresh.
  • Query (record). Query-string parameters, where each field becomes name=value in the URL, properly encoded.
  • Headers (record). Request headers, where each field is one header (use #"User-Agent" style names for hyphens).
  • Content (binary). Supplies a request body, which turns the GET into a POST.
  • ManualStatusHandling (list). Status codes you handle yourself instead of letting them throw, for example {404,500}.
  • Timeout (duration). How long to wait before the request times out, for example #duration(0,0,2,0) for two minutes.
  • IsRetry (logical). When true, skips the cache and forces a fresh fetch, useful for retry logic.
  • ApiKeyName (text). The name of the API key parameter for sources that authenticate with a web API key.
  • ExcludedFromCacheKey (list). Header names to leave out of the cache key, so they don’t fragment caching.

Things to keep in mind with Web.Contents

  • Build dynamic URLs with RelativePath and Query, not string concatenation. Concatenating values into the URL can trigger the Data Privacy firewall and break refresh in the service, because Power Query can no longer see a static base URL.
  • Credentials are tied to the base URL. Keep the first argument a fixed string so your saved credentials keep matching the source across refreshes.
  • POST requests are anonymous only. When you pass Content to make a POST, Power Query uses anonymous authentication, so you cannot combine POST with credentialed auth on the same call.

Performance and query folding

Web.Contents does not fold, since a web response is not a queryable backend. Power Query does cache responses, so repeated calls to the same URL within a refresh can reuse the cached result. Set IsRetry to true to bypass that cache and force a fresh request.

Common questions about Web.Contents

What is the difference between Web.Contents and Web.Page?

Web.Contents downloads the raw response as binary, which you parse yourself, so it suits APIs and files. Web.Page renders an HTML page and pulls out its tables, so it suits scraping a web page’s visible tables.

Why does my Web.Contents refresh fail in the service but work on the desktop?

This is almost always a dynamic-URL problem. If you concatenate values into the URL string, the gateway and Data Privacy firewall cannot verify a static source, so refresh fails. Move the moving parts into RelativePath and Query and keep the base URL a plain constant.

List of All Power Query Functions

Related Power Query Functions / Articles:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.