Error in getting POST request in an API

[gilbertendaya]

Hi,

Need your help with my issue in Power BI.

Here is the M-Script

let
url = URL,

body = "{""limit"": -1,""sort"": {""option"": ""ASC"",""field"": [""FlightDate""},""parameters"": [{}],""range"": [{""FlightDate"": {""from"": ""02/14/2020 14:02"",""to"": ""02/18/2020 00:02""}}]}",
Parsed_JSON = Json.Document(body),
BuildQueryString = Uri.BuildQueryString(Parsed_JSON),
Source = Json.Document(Web.Contents(url, [Headers=[#"Content-Type"="application/json"],Content = Text.ToBinary(BuildQueryString)]))
in
Source

But I'm encountering this error:

DataFormat.Error: We found an invalid array in JSON input.
Details:
Value=
Position=60

What is the problem on this? Please advise.

@ImkeF @tonmcg 
Thank you.

[srinivaspappu]

Hello All,

Can I use M script to make a call to a .NET URL in POST method, by passing the selected filter values of PowerBI report, as JSON / any object format ? Please suggest.

Thanks,

Srinivas Pappu.

[gilbertendaya]

This is working well in Postman but when I translate it to Power BI, the error occurs. I have set a parameter for the URL.

[ImkeF]

Hi @gilbertendaya 

you must have missed/mixed up some brackets here, as the string that step ... produces is not a valid JSON format.

Paste it into a formatter like https://jsonformatter.curiousconcept.com/  and you'll see that the following errors are returned:

[gilbertendaya]

Hi @ImkeF 

I really appreciate your response on this post. 

I have corrected the format but there is another error that I encountered.

Expression.Error: We cannot convert the value -1 to type Text.
Details:
Value=-1
Type=[Type]

I'm really a newbie in Power BI and your reponse is highly appreciated.

Thank you.

[ImkeF]

Hi @gilbertendaya 

there's a lot going wrong here.

How shall your final URL look like?

[gilbertendaya]

When I tried to get the data after this line:

let
url = URL,

//body = "{""limit"": -1,""sort"": {""option"": ""ASC"",""field"": [""FlightDate""},""parameters"": [{}],""range"": [{""FlightDate"": {""from"": ""02/14/2020 14:02"",""to"": ""02/18/2020 00:02""}}]}",
body = "{""limit"": ""-1"",""sort"": {""option"": ""ASC"",""field"": [""FlightDate""]},""parameters"": [{}],""range"": [{""FlightDate"": {""from"": ""02/14/2020 14:02"",""to"": ""02/18/2020 00:02""}}]}",
Parsed_JSON = Json.Document(body),
BuildQueryString = Uri.BuildQueryString(Parsed_JSON)
//Source = Json.Document(Web.Contents(url, [Headers=[#"Content-Type"="application/x-www-form-urlencoded"],Content = Text.ToBinary(BuildQueryString)]))
in
BuildQueryString

The error with below already occurs:

Expression.Error: We cannot convert a value of type Record to type Text.
Details:
    Value=[Record]
    Type=[Type]

[gilbertendaya]

Hi @ImkeF 

Sorry I'm not sure what you mean by final URL? 😞

[ImkeF]

Hi @gilbertendaya 

if you're body shall contain the full JSON record that you've built, then you should reference that directly instead of trying to convert it to a string in step "BuildQueryString". (You'd probably have to convert the -1 back to a number then)

Something like this:

let
url = URL,
body = "{#(cr)#(lf)   ""limit"":-1,#(cr)#(lf)   ""sort"":{#(cr)#(lf)      ""option"":""ASC"",#(cr)#(lf)      ""field"":[#(cr)#(lf)         ""FlightDate""#(cr)#(lf)      ],#(cr)#(lf)      ""parameters"":[#(cr)#(lf)         {#(cr)#(lf)#(cr)#(lf)         }#(cr)#(lf)      ],#(cr)#(lf)      ""range"":[#(cr)#(lf)         {#(cr)#(lf)            ""FlightDate"":{#(cr)#(lf)               ""from"":""02/14/2020 14:02"",#(cr)#(lf)               ""to"":""02/18/2020 00:02""#(cr)#(lf)            }#(cr)#(lf)         }#(cr)#(lf)      ]#(cr)#(lf)   }}",
Parsed_JSON = Json.Document(body),
Source = Json.Document(Web.Contents(url, [Headers=[#"Content-Type"="application/json"],Content = Parsed_JSON]))
in
Source

[gilbertendaya]

Thanks @ImkeF 
I tried the one that you suggested, but below error occurred.

Expression.Error: We cannot convert a value of type Record to type Binary.
Details:
Value=[Record]
Type=[Type]

[ImkeF]

Oh sorry, I missed the magic function Json.FromValue: https://www.thebiccountant.com/2018/06/05/easy-post-requests-with-power-bi-and-power-query-using-jso... 

Source = 
Json.Document( Web.Contents(url, Headers=[#"Content-Type"="application/json"],Content = Json.FromValue(Parsed_JSON)]))

[gilbertendaya]

Hi Sir @ImkeF 

I will continue to say thank you for always answering my queries 🙂
I tried to change the Source line with the one that you've sent but there is an error in advanced editor:

Source = 
Json.Document( Web.Contents(url, Headers=[#"Content-Type"="application/json"],Content = Json.FromValue(Parsed_JSON)]))

I think this is due to the ] in the end.. there should be [ before the word headers, right?

Source = 
Json.Document( Web.Contents(url, [Headers=[#"Content-Type"="application/json"],Content = Json.FromValue(Parsed_JSON)]))

 But when I did this, 400 error occurs..

DataSource.Error: Web.Contents failed to get contents from '<URL>' (400): Bad Request
Details:
DataSourceKind=Web
DataSourcePath=<URL>
Url=<URL>

[ImkeF]

Hi @gilbertendaya ,

now your query has been sent out and you've received an answer from the server. Unfortunately that's all Power Query will tell you about it and you have to use a tool like Fiddler to see more details of what's going wrong.

I'd recommend to check the structure of the nested JSON first, maybe you swapped a list for a record or so.

Otherwise you'd have to compare the traffic that your successful postmal request causes the traffic from Power BI with Fiddler.

[gilbertendaya]

Hi @ImkeF 

But this is working fine with PostMan 😞 

and then here in Power BI desktop, it is not working 😞

[ImkeF]

Hi @gilbertendaya 

Pretty sure that there is a difference in the code you've used in both versions. 

Checking with Fiddler is the way to go forward if you cannot spot it by code analysis.

[gilbertendaya]

Hi @ImkeF 

What will be the difference with the Fiddler?

In Postman, I just use Post method and the URL and then the body.

It throws the data from the database.

Not sure what will be the difference in it.

[ImkeF · Accepted solution]

Hi @gilbertendaya 

You could simply using different code.

This first example you've posted contained wrong brackets.

Why is that?

Could it be that JSON you've been using in Postman has different nesting structure?

Must your -1 be a text instead of a number?

Honestly, I've been there a couple of times to recognize that a simple typo is throwing things off.
Very had to believe that Power Query is the issue here.

[gilbertendaya]

Hi @ImkeF 

It seems the issue was really the way I copy and use the body from PostMan to Power BI Advanced editor.

This resolves now my issue.

Thank you very much for the assistance. 🙂

[tonmcg]

It's far easier to convert your JSON to a Power Query M record and use that as the `Content` value in `Web.Contents`, like so:

let
    url = URL,
    bodyContent = [
        limit = -1, 
        sort = [
            option = "ASC",
            field = { "FlightDate" },
            parameters = {[]},
            range = {
                [ 
                    FlightDate = [
                        from = "02/14/202014:02",
                        to = "02/18/202000:02"
                    ]
                ]
            }
        ]
    ],
    binaryContent = Json.FromValue(bodyContent),
    request = Web.Contents(
        url, 
        [
            Headers = [
                #"Content-Type"="application/json"
            ],
            Content = binaryContent
        ]
    ),
    jsonResponse = Json.Document(request)
in
    jsonResponse

Some advantages:

• you can parameterize values within the record; e.g., the values for the `from` and `to` fields
• you can use native Power Query M `Record` functions to update the record easily
• you don't have to manually parse a JSON string and enclose parts of it with two double quotes ("")
• debugging your code is far easier

To convert JSON to a record in Power Query M, remember that a JSON object is a Power Query M record and a JSON array is a Power Query M list. In other words, {} = [] and [] = {}.

[gilbertendaya]

What I'm trying to achieve here is to retrieve the data from DynamoDB based on the URL and the body that I have that contains the date range.

[gilbertendaya]

Hi @ImkeF 

I have changed it to ""-1"" and other error occurs:

Expression.Error: We cannot convert a value of type Record to type Text.
Details:
Value=[Record]
Type=[Type]