When downloading data from the web, it’s often best to grab the data from APIs that are designed for machine-to-machine communication than from the site that’s actually visible on the screen. Not only is the download usually faster, but you also often get more additional parameters that can be very useful. In this article I’m going to show you how to retrieve the relevant URLs for downloading files from webpages (without resorting to external tools like Fiddler) and how to tweak them to your needs.
Retrieving the URL to download files from webpages
Say I want to download historical stock prices from this webpage:
The screen will show a link to a download: Read more
The function Json.FromValue provides a super-easy way to create POST-calls to web services that require JSON format in their body parameters.
If you want to make a POST request to a web service through Power Query, you have to add the relevant data in the “Content”-parameter of the query (see Chris Webb’s article here for example). This is a pretty nifty method that transforms the default GET-method to a POST automatically. The content of that parameter has to be passed in as a binary and therefore the Text.ToBinary function can be used. This will serve well in many cases, but if your service requires a JSON record and you happen to have that record somewhere in your query already, transforming it to text can get pretty cumbersome and is actually not necessary:
Say you want to use Microsoft’s Translate API to translate values from a column to a different language. This API lets you pass in multiple strings into one call if you pass them in as a JSON array of records. So instead of transforming them all into a long string of text that represents the JSON-syntax, you can simply let come
Json.FromValue to the rescue
List.Transform ( YourColumn, each [Text=_] )
will transform “YourColumn” into a list of records that represents the required JSON-array.
The function Json.FromValue (which hides itself in the Text-category of M-functions) takes actually in ANY format from Power Query and transforms it into a binary JSON-format. Pass this into the Content-parameter and you’re good to go.
Note: There is a little flaw with the current version of the MS Translate API and in my next blogpost I will show how to tackle it.
Enjoy & stay queryious 😉