The function Json.FromValue provides a super-easy way to create POST-calls to web services that require JSON format in their body parameters.

Background

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:

Problem

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 😉