Tag: REST API

Categories
Adventures In Power Query Uncategorized

Capturing Metadata of your Web.Contents calls – Using REST APIs in Power Query

Screenshot Viewing the metadata record in Power Query
Viewing the metadata record from Value.Metadata( Web.Contents( .. ))

Summary

Any value in Power Query can store info in a metadata record. “Datasources” often have extra info. Web.Contents exposes the full request url including the Query variables, request headers, HTTP Status codes, and more.

You can view the final query here:

Finding the binary step

The core “trick” to making this work is calling Value.Metadata on the right step.

For Web.Contents and File.Contents you want the binary value they return, before converting the response

For Folder.Files and Folder.Contents you want the binary value in the column named [Content]

Screenshot of Find Folders Content
Finding the binary Content columns

Splitting Web.Contents into steps

Lets call this Rest API to some JSON: https://httpbin.org/json
If you add a query using the Web UI, it combines “download raw bytes” and “convert to json” as one step. 


let
    Source = Json.Document(Web.Contents("https://httpbin.org", [RelativePath = "/json"] ))
in
    Source

To access the response, we need to split them up:


let
    ResponseBytes = Web.Contents("https://httpbin.org", [RelativePath = "/json"] ),
    Source = Json.Document( ResponseBytes )
in
    Source

Reading the Metadata

First call Value.Metadata on our first step. It returns a regular record.
You can drill down to copy important properties to the top level

Screenshot Viewing the metadata record in Power Query
Viewing the metadata record from Value.Metadata( Web.Contents( .. ))

[
    BaseUrl      = "https://httpbin.org",
    RawBytes     = Web.Contents( BaseUrl, [ RelativePath = "/json" ]), // type is: binary|error
    ResponseMeta = Value.Metadata( RawBytes ), // type is: record
    Json         = Json.Document( RawBytes ),  // type is: record/list/error

    // Now lets grab important fields by drilling into the ResponseMeta record
    StatusCode     = ResponseMeta[Response.Status]?, // type is: text

    // Content.Uri is a function that returns the full filepath for files,
    // For web requests you get the full url including the value of the query string
    FullRequestUrl = ResponseMeta[Content.Uri]() // type is: text
]

Decoding the response as raw text for debugging

Say your API returns HTML when you expect JSON
Some APIs will return errors as HTML, so if something isn’t right it’s worth checking.

That causes Json.Document to fail. Because we split steps up — RawText continues to work. It’s a quick way to verify if it’s actually returning CSV / JSON / HTML / etc.

If your files aren’t using UTF8 you’ll need to include the encoding parameter. TextEncoding.Utf8 is a good default for the web

RawText = Text.FromBinary( RawBytes ), // type is: text/error

Tip: The UI might use Lines.FromBinary, which means you need to combine them to one string. Text.FromBinary saves you a step.

Related Posts

Inspecting Function “subtypes” in Power Query

Related Links

Related Functions