Easy POST requests with Power BI and Power Query using Json.FromValue

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 😉

Comments (4) Write a comment

  1. Hi Imke,

    thank you for the post.
    I am trying to apply this to the following site:

    https://openfigi.com/api#introduction

    In particular, when trying to get the mapping data (https://openfigi.com/api#post-v1-mapping), it requires a post-request.

    So I built this, to mimic the first example:

    let
    Json = Json.FromValue([idType= “ID_ISIN”, idValue= “US4592001014”]),
    Source = Web.Contents(“https://api.openfigi.com/v1/mapping?”,[Content=Json])
    in
    Source

    However, I get a (400) Bad request response. Fiddler tells me that it is because “Request body must be a valid JSON array.”

    Any ideas on how to deal with this? Thanks.

    Reply

    • Hi Wouter,
      this API takes in a list of records as parameters and you provided just a record. Also you have to pass the content-type in the headers as well.
      So this query will work (there you see how convenient it is to pass multiple requests into one query as well 😉 :

      let
      Json = Json.FromValue({[idType= "ID_ISIN", idValue= "US4592001014"], [idType = "ID_WERTPAPIER", idValue = "851399", exchCode = "US" ]}),
      Source = Json.Document(Web.Contents("https://api.openfigi.com/v1/mapping?", [Headers=[#"Content-Type"="application/json"], Content=Json]))
      in
      Source

      Reply

      • Thank you, Imke, that makes absolute sense. Thank you for adding the header-part, that would have been my next hurdle :-). Unfortunately M is not a target language when you see examples in the API-documentation… Time to create some custom connectors and make them available.

        Reply

Leave a Reply

Die Datenschutzbestimmungen finden Sie hier: / Please find the privacy policy here: https://wp.me/P6lgsG-Rz