Connecting API

[SomeDataDude]

Hello,

I've read through several posts here but can't figure it out. I want to realize an API connection in Power BI. I have a GET API and a POST API. I can't get both to work. For the POST API I tried the web option and the M code. I tried the following in M ​​code:

let
URL = "https://url.com",
headers = [#"Content-Type"="application/json", #"Authentication"="Basic *****"],
data = Json.FromValue([apiKey = "MyApiKey"]),
web = Web.Contents(URL, [ Content = data, Headers = headers, ManualStatusHandling = {404, 400}]),
result = Json.Document(web)
in
result

I get the following error message

"The credentials provided are invalid."

With the web option I get the error:

"A web API key can only be specified when a web API key name is provided"

I know these credentials are good. Because the API runs in another BI tool.

What am I doing wrong? Thanks in advance for your response.

[SomeDataDude]

Hello,

I have the API working via Postman. To get the API working, an API key, Authorization and a body must be entered. How can I process this in the M code?

Postman:

I have now written the following in the M code:

let
content = "{
""method"": ""get"",
""body"": {
""mode"" : ""raw"",

""ColCoCode"": ""00"",
""PayerNumber"" : ""AA00000000"",
""InvoiceStatus"": ""A"",
""FromDate"":""2021-12-12"",
""PageSize"":""-1""

}
}",

Source = Json.Document(Web.Contents("https://url.api",
[Headers=[#"Authorization"="Basic****", #"apikey" = "****", #"Content-Type"="application/json"], ManualStatusHandling = {404, 400},

Content=Text.ToBinary(content)]))
in
Source

Looks like I'm making progress. However, I get the following error in Power BI:

In Postman I see data with status A. If I change it to B I get the following error:

This is also correct. Since only data under status A is visible. But in Power BI, regardless of the status, I get the error on Invoice Status.

It seems I'm missing something in the M code. Can someone please help me?

Thanks in advance!

[SomeDataDude · Accepted solution]

I have found the solution. The following M code works for the API:

let
content = "{
            ""mode"" : ""raw"",
            ""ColCoCode"": ""00"",
            ""PayerNumber"" : ""AA00000000"",
            ""InvoiceStatus"": ""A"",
            ""FromDate"":""2021-12-12"",
            ""PageSize"":""-1""
}",

Source = Json.Document(Web.Contents("https://url.api",
                       [Headers=[#"Authorization"="Basic****", 
                                 #"apikey" = "****", 
                                 #"ContentType"="application/json"], 
                                  
                        ManualStatusHandling = {404, 400},

Content=Text.ToBinary(content)]))

in
Source

[parry2k]

@SomeDataDude not sure why you have authentication type in the header since you are using API Key.

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

[SomeDataDude]

@parry2k without the authentication I get the following message: Please specify how to connect

let
URL = "https://url.com",
headers = [#"Content-Type"="application/json"],
data = Json.FromValue([apiKey = "MyApiKey"]),
web = Web.Contents(URL, [ Content = data, Headers = headers, ManualStatusHandling = {404, 400}]),
result = Json.Document(web)
in
result