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

      • Hi! Thanks for sharing just want to ask that how can i pass date variables like fromDate and toDate in body to get the data of particular range.

        Reply

        • Hi monis,
          you have to include the reference to the parameter in the original text itself, before you use the Json.FromValue-function.

          Therefore you have to break the escape and combine with ampersand (&) like so “Some text ” & fromDate & “further text…” & toDate
          /Imke

          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

  4. Hi Imke, I have an excel file that is essentially a template for non-technical users to update some definitions, and I use power query to dynamically create DAX EVALUATE queries and DAX measures resulting from the user inputs, to be reflected in a pivot table from the Power Pivot model. I am hoping to format the DAX measures by passing the string to the SQLBI DAX formatter API from PQ, so that the final measure is readable. I am loading the final measures to a hidden tab in the workbook and using VBA to add to the Power Pivot model, but the measures can be very long (a SWITCH measure with many conditions).

    I have tried numerous attempts at this but cannot get it to work.

    Reply

  5. Hi ,

    i unable to get JSON file from power BI to pass it to the flow..any updates to do that

    Reply

  6. Hi Imke,

    I am creating a custom connector for Using REST API POST method.
    I am able to connect to the datasource using API provided , since API only supports Import Query option .
    I cant do query unfolding, currently i am passing the table information in Body part of API and getting the required table.

    I want this to happen dynamically , i should be able to get all the tables inside the database which i am connecting to using REST API .

    Is there any way to achieve this ?

    Thanks

    Reply

  7. Hi Imke,

    Thanks for your post 🙂
    I am trying to do a similar connection to an url, however I don’t see the table with the information that I need.
    This is the code I am using:

    let
    url = “https://urladdress”,
    body = “{ “”statisticType””: “”[“”CMD_Stock””]””, “”startDate””: “”2020-04-07″” , “”endDate””: “”2020-04-07″” }”,
    Source = Json.Document(Web.Contents(url,[Headers = [#”Content-Type”=”application/json”], Content = Text.ToBinary(body) ] ))
    in
    Source

    I need to obtain the following structure in Power BI (a table with three columns):

    statisticType startDate endDate
    CMD 07/04/2020 07/04/2020
    CMD 07/04/2020 07/04/2020
    CMD 07/04/2020 07/04/2020
    CMD 07/04/2020 07/04/2020
    CMD 07/04/2020 07/04/2020

    Can you help me? Thanks mdsr.

    Reply

  8. Dear Imka,

    Firstly thank you the great help with this…

    I seem to be getting some issues with my solution.

    let
    Source = (Records) =>
    let
    Key = “{API Key}”, //this is the API for the live environment
    CompanyID = “XXXXXX”, //this is the company code
    Method = “DetailedLedgerTransaction”, //this is from the Sage Accounting API Specification
    WebAddress = “https://accounting.sageone.co.za/api/2.0.0/”, //This is the webaddress for the live environment
    url = Json.Document(Web.Contents(WebAddress & Method & “/Get?apikey=” & Key & “&CompanyID=” & CompanyID)),
    header = [#”Content-Type”=”application/json”,#”Accept”=”application/json”], //not sure about this header???
    body = Json.FromValue([ Inactive = [ true ], Active = [ true ], FromAccount = {“”}, ToAccount = {“”}, IncludeNoTax = [ true ], FromDate = {“2021-12-30”}, ToDate = {“2021-12-30”}]),
    Source = Json.Document(Web.Contents(url,[Headers = header,Content=body])
    in
    Source
    in
    Source

    I get a not allowed error.

    Any idea why I might be getting this?

    Thanks in advance!

    Reply

  9. Hi,
    Does anyone know how to deal with this error:

    DataSource.Error: Web.Contents failed to get contents from ‘https://api.openfigi.com/v1/mapping’ (405): Method Not Allowed

    Thanks

    Reply

Leave a Reply