POST request and JSON body with several elements

[crossover]

HI! I'm in uncharted waters here, but hoping to find some help. I'm trying to create an end-user tool to run HTTP POST requests for entering new records in REST API over PowerQuery. The general usage is Excel-centric and HTTP GET request already runs fine over PQ, so I was hoping to keep using the same toolset.

I found a nice video for POST requests over PowerQuery here: Making a POST Request Using Power Query - YouTube

Unfortunately it didn't help me far along as it just passes a single value to the API (Json.FromValue). Meanwhile I need to pass an entire JSON body. Not an enormous bunch of data, but still a handful of elements. I'd expect data to reside in Excel table or separate ranges, but not really sure how to go about converting it all into a single JSON body and pass onwards with POST request.

Would Python be a must here? Any thoughts on how to go about it? JSON body is something like this (I have run it via POSTMAN and it works fine).

{
        "account": "https://myapi.com/id1/",
        "contact": {
            "name": "MyContact",
            "phone": ""
        },
        "address": {
            "raw_address": "Myaddress"
        },
        "description": "",
        "complete_after": "2021-03-01T02:39:04.234330+02:00",
        "state": "unassigned",
        "metafields": {
            "account:id1": "123",
            "account:id2": "ABC",
        }
    }

[crossover]

Hello @ImkeF and others!

Thanks for the input. Unfortunately I'm still struggling to get it to work. I have teared my POST request JSON to a bare minimum that API accepts via Postman and I'm trying to build on that in PowerQuery:

{
  "account": "https://myservice.com/api/tasks/accounts/123/",
  "address": {
    "raw_address": "10 greetings from Estonia"
  }
}

This is what I came up with in PowerQuery, however PQ doesn't even launch the query and points towards the colon in front the "account": (comma expected). When replacing all colons with commas, I end up with Error 400 (Bad request). Am I missing something very minor here or is much more extensive nesting needed? In practice, I'd need to compile JSON maybe 3-4x as long.

let
    Source = Json.Document(Web.Contents(
           "https://myservice.com/api/tasks/",
           [
           
            Headers = [#"Authorization"= MyToken,
            #"Content-Type"="application/json"],
    
            Content=Json.FromValue({"account":"https://myservice.com/api/tasks/accounts/123/","address":{"raw_address":"10 greetings from Estonia"}})       
           ]
    ))
in
    Source

[ImkeF · Accepted solution]

Hello @crossover ,

you have to use the M-syntax if you want to use Json.FromValue like so:

let
    Source = Json.Document(Web.Contents(
           "https://myservice.com/api/tasks/",
           [
           
            Headers = [#"Authorization"= MyToken,
            #"Content-Type"="application/json"],
    
            Content=Json.FromValue([account = "https://myservice.com/api/tasks/accounts/123/", address = [raw_address = "10 greetings from Estonia"]])       
           ]
    ))
in
    Source

Alterntatively, the Text.ToBinary-function would would with the original JSON syntax like so:

Content= Text.ToBinary("{""account"":""https://myservice.com/api/tasks/accounts/123/"",""address"":{""raw_address"":""10 greetings from Estonia""}}")

[crossover]

@ImkeF thank you so much! Both options work fine in basic form, but looks like M-syntax is easier to read. I also had problems adding parameter values (address etc) into the code with Text.ToBinary and found this to be easier with native M, since the query ultimately needs parameterized values.

I really hate bugging you more, but I ran into some more trouble when building up from the working base. How should I handle JSON objects with a colon in M? This is a snippet of JSON that works over Postman:

{
        "account": "https://myservice.com/api/tasks/accounts/123/",
        "contact": {
            "name": "task Name",
            "phone": "555-123"
        },
        "address": {
            "raw_address": "10 greetings from Estonia"
        },
        "metafields": {
            "account:sn": "123",
            "account:project": "PR1",
            "account:phase": "1"
        }
    }

I have an error in metafields section and apparently problem is the colon sign, e.g account:sn, account:project, account:phase objects. Do I somehow need to hide the colon since I'm getting an "invalid identifier" error message about "account:sn" 

    Source = Json.Document(Web.Contents(
           "https://myservice.com/api/",
           [
           
            Headers = [#"Authorization"= MyToken,
            #"Content-Type"="application/json"],
    
            Content= Json.FromValue([account = "https://myservice.com/api/tasks/accounts/123/",
                                    contact = [name = "taskName", phone = "555-123"],
                                    address = [raw_address = API_address],
                                    metafields = [account:sn = "123", account:project = "PR1", account:phase = "1"]
                                    ])   
           ]
    )),
    #"Converted to Table" = Record.ToTable(Source)
in
    #"Converted to Table"

[ImkeF]

Hi @crossover ,

I believe you have to escape strings with colons like so: #"Text1:Text2"

    Source = Json.Document(Web.Contents(
           "https://myservice.com/api/",
           [
           
            Headers = [#"Authorization"= MyToken,
            #"Content-Type"="application/json"],
    
            Content= Json.FromValue([account = "https://myservice.com/api/tasks/accounts/123/",
                                    contact = [name = "taskName", phone = "555-123"],
                                    address = [raw_address = API_address],
                                    metafields = [#"account:sn" = "123", #"account:project" = "PR1", #"account:phase" = "1"]
                                    ])   
           ]
    )),
    #"Converted to Table" = Record.ToTable(Source)
in
    #"Converted to Table"

[Smauro]

Hi @crossover ,

What you're typing inside Json.FromValue() is a what M calls a record. Records, as much as almost everything in PQ syntax, use identifiers.
So, on the left of each record element, a valid Identifier should be present.
account:sn is not valid because it contains a colon. Just turn that into #"account:sn" .

In case you'd like to learn almost everything about identifiers, here's a thorough explanation.

Cheers,

[ImkeF]

Hi @crossover ,

just create the nested object in Power Query and then use the Json.FromValue function to transform it into the format for your call: Easy POST requests with Power BI and Power Query using Json.FromValue – The BIccountant