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 (8) 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

  2. Hi Imke

    I read this post hoping I had finally found a way to make Power Query convert a column input as a List Array – but sadly no – or am I missing something?

    I have a column – [Totaling] – with values like “100..998|1050..1099” (account number ranges) – and I want those number ranges converted to lists and expanded – but whatever method I try to use – the values always end up being of type text – not type list – and can’t be expanded.

    I have tried:
    = Table.TransformColumns(Source,{“Totaling”, each if Value.Is( _, type text) then {_} else null})

    Or:
    = List.Transform ( Source[Totaling], each Json.FromValue({_}) )

    I would love some advise on how to turn those number ranges into lists – that can be expanded.

    Reply

  3. I am VERY grateful for this. Thank you so much, Imke.

    Now I can easily build the AccountsAllocation tables from your Easy Profit & Loss series – which has provided me and colleagues with invaluable solutions.

    BTW – I also turned Dynamics AX Ledgers into same [Totaling] structure using SQL – feel free to make this into Power Query function:

    with cte_ax_ledger as (
    SELECT l.*, r.ACCOUNTNUM
    FROM [AxDK].[LEDGERTABLEINTERVAL] l
    JOIN [AxDK].[LEDGERTABLE] r on l.ACCOUNTRECID = r.RECID
    — WHERE LTRIM(r.ACCOUNTNUM) = ‘31195’
    )
    SELECT DISTINCT [DATAAREAID], [ACCOUNTNUM], [Totaling]
    FROM cte_ax_ledger p1
    CROSS APPLY ( SELECT
    STUFF( (SELECT CONCAT(‘|’, LTRIM(FROMACCOUNT), ‘..’, LTRIM(TOACCOUNT))
    FROM cte_ax_ledger p2
    WHERE p2.[DATAAREAID] = p1.[DATAAREAID]
    AND p2.[ACCOUNTNUM] = p1.[ACCOUNTNUM]
    ORDER BY [FROMACCOUNT]
    FOR XML PATH(”), TYPE).value(‘.’, ‘varchar(max)’)
    ,1,1,”)
    ) D ( Totaling )

    Reply

    • Thanks Claus and glad it helped 😉
      I’m not familiar with the way this is solved in AX. Do you have a URL that shows it by any chance?
      Thanks and cheers,
      Imke

      Reply

Leave a Reply

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