Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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!
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 not sure why you have authentication type in the header since you are using API Key.
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!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |