Web.Contents

Updated on

Web.Contents is a Power Query M function that downloads content from a URL and allows customization through various optional parameters. The function returns binary content from the specified URL along with HTTP response headers as metadata.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

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

Description

Returns the contents downloaded from url as binary. An optional record parameter, options, may be provided to specify additional properties. The record can contain the following fields:

  • Query: Programmatically add query parameters to the URL without having to worry about escaping.
  • ApiKeyName: If the target site has a notion of an API key, this parameter can be used to specify the name (not the value) of the key parameter that must be used in the URL. The actual key value is provided in the credential.
  • Headers: Specifying this value as a record will supply additional headers to an HTTP request.
  • Timeout: Specifying this value as a duration will change the timeout for an HTTP request. The default value is 100 seconds.
  • ExcludedFromCacheKey: Specifying this value as a list will exclude these HTTP header keys from being part of the calculation for caching data.
  • IsRetry: Specifying this logical value as true will ignore any existing response in the cache when fetching data.
  • ManualStatusHandling: Specifying this value as a list will prevent any builtin handling for HTTP requests whose response has one of these status codes.
  • RelativePath: Specifying this value as text appends it to the base URL before making the request.
  • Content: Specifying this value changes the web request from a GET to a POST, using the value of the option as the content of the POST.

The HTTP request is made as either a GET (when no Content is specified) or a POST (when there is Content). POST requests may only be made anonymously.

The headers of the HTTP response are available as metadata on the binary result. Outside of a custom data connector context, only a subset of response headers is available (for security reasons).

Examples

Retrieve the contents of "https://bing.com/search?q=Power+Query" using the RelativePath and Query options. These options can be used to dynamically query a static base URL.

// Output: binary
let
    searchText = "Power Query"
in
    Web.Contents( 
        "https://www.bing.com",
        [
            RelativePath = "search",
            Query = [q = searchText]
        ]
     )

Perform a POST against a URL, passing a binary JSON payload and parsing the response as JSON.

// Output: table
let
    url = ...,
    headers = [#"Content-Type" = "application/json"],
    postData = Json.FromValue( [x = 235.7, y = 41.53] ),
    response = Web.Contents( 
        url,
        [
            Headers = headers,
            Content = postData
        ]
     ),
    jsonResponse = Json.Document( response )
in
    jsonResponse

Connect to a secure URL that accepts an authentication key as part of its query string. Instead of hard-coding the secret key in M ( which would pose a security risk ), the key can be provided securely by specifying its name ( not its value ) in M, choosing Web API authentication, and entering the key value as part of the Web API credential. When used in this way, the following example will generate a request to "https://contoso.com/api/customers/get?api_key=******".

// Output: binary
Web.Contents( "https://contoso.com/api/customers/get", [ApiKeyName="api_key"] )

Learn more about Web.Contents in the following articles:

Other functions related to Web.Contents are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/web-contents