error in Power Query M - getting data from API with JSON passed as body raw

[paulciorgovean]

I can do this API in Postman

POST API

URL: https://URL/portal/api/checkKanbanPart/

Body / Raw

{

    "Authorization": {

        "User": "firstname.lastname",

        "Pass": "password",

        "Key": "keyvalue"

    },

    "Part": {

        "Sku": "12345"

    }

}

I try to do this in M language:

let
url = "https://URL/portal/api/checkKanbanPart/",
body = "
""Authorization"": {""User"" : ""firstname.lastname"", ""Pass"" : ""password"", ""Key"" : ""keyvalue""},
""Part"" : {""Sku"" : ""12345""}",
Parsed_JSON = Json.Document(body),
BuildQueryString = Uri.BuildQueryString(Parsed_JSON),
Source = Json.Document(Web.Contents(url,[Headers = [#"Content-Type"="application/json"], Content = Text.ToBinary(body) ] ))
in
Source

but I get a Bad request error.

How can I write this in M Language in Power Query?

[paulciorgovean]

thank you for your answer Pat,

It seems to build the json correctly but I get an error in the last step

"DataFormat.Error: We found extra characters at the end of JSON input.
Details:
Value={
Position=106"

here is my query in Power BI and my API call in Postman.

[ppm1]

I would remove the Json.Document from the last step and see what you are getting from Web.Contents alone. Are you getting a successful response or not? If so, is it JSON format?

Pat

[paulciorgovean]

Well, it turns out, the problem is the format of the API response. I copied the response into a file.json and tried to import it in PowerBI. It gave me the same error. 

Then I found this JSON format validator and found out there are some extra quotation marks. See screenshot.

I made the changes in the file.json and it pickes it up.

A workaround I found was to get the web.contents as you suggested and transform it into text. Then I break the text by delimiters and I get me values from there.

It's ugly but it works.

Another way is to follow up with the supplier of the API and get them to correct their format.

In the end, your solution worked, so thank you very much! I'll mark it as accepted.

[ppm1 · Accepted solution]

Try building your JSON using records instead.

let
url = "https://URL/portal/api/checkKanbanPart/",
body = [Authorization = [User = "firstname.lastname", Pass = "password", Key = "keyvalue"],
Part = [Sku = "12345"]],

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

Pat