{"id":2769,"date":"2024-06-01T20:45:28","date_gmt":"2024-06-02T01:45:28","guid":{"rendered":"http:\/\/ninmonkeys.com\/blog\/?p=2769"},"modified":"2024-06-03T18:28:29","modified_gmt":"2024-06-03T23:28:29","slug":"power-query-capturing-response-metadata-from-web-contents","status":"publish","type":"post","link":"https:\/\/ninmonkeys.com\/blog\/2024\/06\/01\/power-query-capturing-response-metadata-from-web-contents\/","title":{"rendered":"Capturing Metadata of your Web.Contents calls &#8211; Using REST APIs in Power Query"},"content":{"rendered":"\n\n\n<h2>Summary<\/h2>\n\n\n\n<p>Any value in Power Query <a href=\"https:\/\/bengribaudo.com\/blog\/2021\/03\/17\/5523\/power-query-m-primer-part20-metadata\">can store info in a <strong>metadata<\/strong> <code class=\"\" data-line=\"\">record<\/code><\/a>. &#8220;Datasources&#8221; often have extra info. <code class=\"\" data-line=\"\">Web.Contents<\/code> exposes the full <strong>request url<\/strong> including the <code class=\"\" data-line=\"\">Query<\/code> variables, request headers, HTTP Status codes, and more.<\/p>\n\n\n\n<p>You can view the final query here: <\/p>\n\n\n\n<ul><li><a href=\"https:\/\/github.com\/ninmonkey\/Ninmonkey.PowerQueryLib\/blob\/18e53723dda0ee358e8c23fa3561b9829a4ee47a\/Examples-Blog\/Using%20Web%20APIs%20in%20Power%20Query\/Part%2001%20-%20Capturing-Response-Metadata.pq\">Part 1 &#8211; Capturing Response Metadata.pq<\/a> or <a href=\"https:\/\/github.com\/ninmonkey\/Ninmonkey.PowerQueryLib\/blob\/951d1755b26920748f9352c3c277c60fdbc4475e\/Examples-Blog\/Using%20Web%20APIs%20in%20Power%20Query\/Part%2001%20-%20Capturing-Response-Metadata.pbix\">as a pbix<\/a><\/li><\/ul>\n\n\n\n<h2>Finding the binary step<\/h2>\n\n\n\n<p>The core &#8220;trick&#8221; to making this work is calling <a href=\"https:\/\/learn.microsoft.com\/en-us\/powerquery-m\/value-metadata\">Value.Metadata<\/a> on the right step.<\/p>\n\n\n\n<p>For <strong>Web.Contents<\/strong> and <strong>File.Contents<\/strong> you want the <strong>binary <\/strong>value they return, before converting the response<\/p>\n\n\n\n<p>For <strong>Folder.Files<\/strong> and <strong>Folder.Contents<\/strong> you want the <strong>binary<\/strong> value in the column named <strong>[Content]<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" width=\"721\" height=\"404\" src=\"http:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2024\/06\/Folder.Files-Select-Content-Column.png\" alt=\"Screenshot of Find Folders Content\" class=\"wp-image-2779\" srcset=\"https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2024\/06\/Folder.Files-Select-Content-Column.png 721w, https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2024\/06\/Folder.Files-Select-Content-Column-300x168.png 300w\" sizes=\"(max-width: 721px) 100vw, 721px\" \/><figcaption>Finding the binary Content columns<\/figcaption><\/figure>\n\n\n\n<h2><strong>Splitting Web.Contents into steps<\/strong><\/h2>\n\n\n\n<p>Lets call this Rest API to some JSON: <a href=\"https:\/\/httpbin.org\/json\">https:\/\/httpbin.org\/json<\/a><br>If you add a query using the Web UI, it combines &#8220;download raw bytes&#8221; and &#8220;convert to json&#8221; as one step. <\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-powerquery\" data-line=\"\">\nlet\n    Source = Json.Document(Web.Contents(&quot;https:\/\/httpbin.org&quot;, [RelativePath = &quot;\/json&quot;] ))\nin\n    Source\n<\/code><\/pre>\n\n\n\n<p>To access the response, we need to split them up:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-powerquery\" data-line=\"\">\nlet\n    ResponseBytes = Web.Contents(&quot;https:\/\/httpbin.org&quot;, [RelativePath = &quot;\/json&quot;] ),\n    Source = Json.Document( ResponseBytes )\nin\n    Source\n<\/code><\/pre>\n\n\n\n<h2>Reading the Metadata<\/h2>\n\n\n\n<p>First call Value.Metadata on our first step. It returns a regular record.<br>You can drill down to copy important properties to the top level<\/p>\n\n\n\n<figure class=\"wp-block-image alignwide size-full\"><img loading=\"lazy\" width=\"1186\" height=\"567\" src=\"https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2024\/06\/Blog-Post-Header-Image.2024-05-31.png\" alt=\"Screenshot Viewing the metadata record in Power Query\" class=\"wp-image-2775\" srcset=\"https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2024\/06\/Blog-Post-Header-Image.2024-05-31.png 1186w, https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2024\/06\/Blog-Post-Header-Image.2024-05-31-300x143.png 300w, https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2024\/06\/Blog-Post-Header-Image.2024-05-31-1024x490.png 1024w, https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2024\/06\/Blog-Post-Header-Image.2024-05-31-768x367.png 768w\" sizes=\"(max-width: 1186px) 100vw, 1186px\" \/><figcaption>Viewing the metadata record from Value.Metadata( Web.Contents( .. ))<\/figcaption><\/figure>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-powerquery\" data-line=\"\">\n[\n    BaseUrl      = &quot;https:\/\/httpbin.org&quot;,\n    RawBytes     = Web.Contents( BaseUrl, [ RelativePath = &quot;\/json&quot; ]), \/\/ type is: binary|error\n    ResponseMeta = Value.Metadata( RawBytes ), \/\/ type is: record\n    Json         = Json.Document( RawBytes ),  \/\/ type is: record\/list\/error\n\n    \/\/ Now lets grab important fields by drilling into the ResponseMeta record\n    StatusCode     = ResponseMeta[Response.Status]?, \/\/ type is: text\n\n    \/\/ Content.Uri is a function that returns the full filepath for files,\n    \/\/ For web requests you get the full url including the value of the query string\n    FullRequestUrl = ResponseMeta[Content.Uri]() \/\/ type is: text\n]\n<\/code><\/pre>\n\n\n\n<h2>Decoding the response as raw text for debugging<\/h2>\n\n\n\n<p>Say your API returns <strong>HTML<\/strong> when you expect <strong>JSON<\/strong><br>Some APIs will return errors as HTML, so if something isn&#8217;t right it&#8217;s worth checking.<br><br>That causes <strong>Json.Document<\/strong> to fail. Because we split steps up &#8212; <strong>RawText<\/strong> continues to work. It&#8217;s a quick way to verify if it&#8217;s actually returning CSV \/ JSON \/ HTML \/ etc.<\/p>\n\n\n\n<p>If your files aren&#8217;t using <strong>UTF8<\/strong> you&#8217;ll need to include the encoding parameter. <strong>TextEncoding.Utf8<\/strong> is a good default for the web<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-powerquery\" data-line=\"\">RawText = Text.FromBinary( RawBytes ), \/\/ type is: text\/error<\/code><\/pre>\n\n\n\n<p>Tip: The UI might use <strong>Lines.FromBinary<\/strong>, which means you need to combine them to one string. <strong>Text.FromBinary<\/strong> saves you a step. <\/p>\n\n\n\n<h2>Related Posts<\/h2>\n\n\n\n<figure class=\"wp-block-embed is-type-wp-embed is-provider-ninmonkey wp-block-embed-ninmonkey\"><div class=\"wp-block-embed__wrapper\">\n<blockquote class=\"wp-embedded-content\" data-secret=\"X99HrwREWO\"><a href=\"http:\/\/ninmonkeys.com\/blog\/2022\/09\/24\/inspecting-function-subtypes-in-power-query\/\">Inspecting Function &#8220;subtypes&#8221; in Power Query<\/a><\/blockquote><iframe class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; clip: rect(1px, 1px, 1px, 1px);\" title=\"&#8220;Inspecting Function &#8220;subtypes&#8221; in Power Query&#8221; &#8212; Ninmonkey\" src=\"http:\/\/ninmonkeys.com\/blog\/2022\/09\/24\/inspecting-function-subtypes-in-power-query\/embed\/#?secret=X99HrwREWO\" data-secret=\"X99HrwREWO\" width=\"580\" height=\"327\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe>\n<\/div><\/figure>\n\n\n\n<h2>Related Links<\/h2>\n\n\n\n<ul><li><a href=\"https:\/\/www.reddit.com\/r\/PowerBI\/comments\/17tynw5\/i_made_a_template_for_calling_web_apis_using\/\">I made a Template for Calling Web APIs using data-driven Lists &#8211; reddit\/r\/PowerBI<\/a><\/li><li><a href=\"https:\/\/bengribaudo.com\/blog\/2021\/03\/17\/5523\/power-query-m-primer-part20-metadata\">Power Query M Primer &#8211; Part 20: Metadata &#8211; Ben Gribaudo<\/a><\/li><li><a href=\"https:\/\/developer.mozilla.org\/en-US\/docs\/Web\/HTTP\/Status\">Http Status Codes are explained here &#8211; mozilla.org<\/a><\/li><li><a href=\"https:\/\/docs.microsoft.com\/en-us\/powerquery-m\/metadata\">About Metadata in Power Query &#8211; Microsoft Docs<\/a><\/li><li><a href=\"https:\/\/learn.microsoft.com\/en-us\/powerquery-m\/m-spec-types\">Primitive Datatypes in Power Query &#8211; Microsoft Docs<\/a><\/li><li><a href=\"https:\/\/github.com\/ninmonkey\/Ninmonkey.PowerQueryLib\/blob\/b3bfd5264e73ce97f1d988c35167f977d30d5df4\/source\/WebRequest_Simple.pq), and [WebRequest.old.pq](https:\/\/github.com\/ninmonkey\/Ninmonkey.PowerQueryLib\/blob\/b3bfd5264e73ce97f1d988c35167f977d30d5df4\/source\/WebRequest.old.pq\">Alternate versions from years ago: WebRequest_Simple.pq<\/a><\/li><\/ul>\n\n\n\n<h2>Related Functions<\/h2>\n\n\n\n<ul><li><a href=\"https:\/\/learn.microsoft.com\/en-us\/powerquery-m\/file-contents\">[File.Contents]<\/a>, <a href=\"https:\/\/learn.microsoft.com\/en-us\/powerquery-m\/folder-contents\">[Folder.Contents]<\/a><\/li><li><a href=\"https:\/\/learn.microsoft.com\/en-us\/powerquery-m\/json-document\">[Json.Document]<\/a>, <a href=\"https:\/\/learn.microsoft.com\/en-us\/powerquery-m\/text-frombinary\">[Text.FromBinary]<\/a>, <a href=\"https:\/\/learn.microsoft.com\/en-us\/powerquery-m\/web-contents\">[Web.Contents]<\/a><\/li><li><a href=\"https:\/\/learn.microsoft.com\/en-us\/powerquery-m\/value-metadata\">[Value.Metadata]<\/a><\/li><\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Summary Any value in Power Query can store info in a metadata record. &#8220;Datasources&#8221; 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: Part 1 &#8211; Capturing Response Metadata.pq or as a pbix Finding the binary [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":2775,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[150,1],"tags":[6,7,153,152],"_links":{"self":[{"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/posts\/2769"}],"collection":[{"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/comments?post=2769"}],"version-history":[{"count":21,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/posts\/2769\/revisions"}],"predecessor-version":[{"id":2795,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/posts\/2769\/revisions\/2795"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/media\/2775"}],"wp:attachment":[{"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/media?parent=2769"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/categories?post=2769"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/tags?post=2769"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}