Forum Discussion

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

     

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