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]
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
[
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/errorTip: The UI might use Lines.FromBinary, which means you need to combine them to one string. Text.FromBinary saves you a step.
Related Posts
Related Links
- I made a Template for Calling Web APIs using data-driven Lists – reddit/r/PowerBI
- Power Query M Primer – Part 20: Metadata – Ben Gribaudo
- Http Status Codes are explained here – mozilla.org
- About Metadata in Power Query – Microsoft Docs
- Primitive Datatypes in Power Query – Microsoft Docs
- Alternate versions from years ago: WebRequest_Simple.pq