Forum Discussion

SomeDataDude's avatar
SomeDataDude
Advocate I
4 years ago
Solved

Connecting API

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.

Need Help
  • SomeDataDude's avatar
    SomeDataDude
    3 years ago

    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

     

4 Replies

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    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's avatar
      SomeDataDude
      Advocate I
      4 years ago

      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

  • SomeDataDude's avatar
    SomeDataDude
    Advocate I
    3 years ago

    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's avatar
      SomeDataDude
      Advocate I
      3 years ago

      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