Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi guys,
I'm trying to connect to a database on Klipfolio. I'm using my ID as explained here:
https://klipfolio-api-reference-guide.readme.io/docs/data-source-instances-data
so, it's https://app.klipfolio.com/api/1.0/datasource-instances/{id}/data
I'm using this as Access web content. But when I enter the API Key in the Web API area, I get the following error message:
A web API key can only be specified when a web API key name is provided.
What does this mean? What do I need to do?
Hi @akashgera ,
I studied the values that you're passing in the body and they don't look right to me. Also, I don't think that you can pass nested JSON objects via the Text.ToBinary-function.
Instead, you should use the Json.FromValue function with a proper record like so:
...
RequestBody=
[
skuCodes = { },
fromDate = "18/06/2021 15:40:30",
toDate = "23/06/2021 15:40:30",
pageNumber = 1,
dropShipFlag = "no",
locCode = ""
]
webdata = Web.Contents(url, [Content = Json.FromValue(RequestBody), Headers=header]),
...
As you can see, the M language in Power Query uses the opposite characters to define lists/arrays and records compared to JSON:
{ } defines an empty list/array and [ ] a record.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @ImkeF
I need your help in executing the below query which will generate the access token using power query.
After executing the above query it throws as error: webcontents failed to get contents from the given URL
Doing the same in Postman and giving the proper response, check the screenshot below
Pls help!!
Hi @akashgera ,
to troubleshoot I'd recommend to:
- record the web traffic that is generated by the Postman call and the call via Power BI Desktop. (I always use Fiddler for this task, but there are other optionas as well)
- analyse the differences. Maybe it's due to the headers?
- to troubleshoot problems when refreshing in the service, read this:
http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Thanks for the response, I am not much familiar with fiddler but after checking with fiddler, it is showing result-200, Response Header: HTTP/1.1 200 Connection Established
Hi @akashgera ,
difficult to tell.
What I would do next is to use Fiddler to record the web traffic when the successfull call in Postman is made. Then search for "Orgid" and see where this is actually used. This would determine where I integrate it in Power Query then.
Also: The page number should be passed in as number and not as text.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @akashgera ,
I would try the following header:
[#"ApiKey" = ApiKey, #"ApiOwner" = ApiOwner,
#"Content-Type" = "application/x-www-form-urlencoded"]
if that doesn't work, please send a screenshot from the Postman headers (not the body like now)
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hello sir,
After separated both API key and API Owner, now it is throwing an error : Response Code-20 , ResponseMsg- Orgid is mandatory
Please check the screenshots below :-
Hi Folks,
I am using REST API to fetch the data from 3rd party using POST method, I have used the same parameters in Header, Body in postman, and it is giving me the data in JSON format, but when I am using the same thing in power query, it is giving me an error: Response Msg: Invalid APi credentials
Response code:11
response : NULL
Please have a look in my power query below and help me what is wrong in this :-
let
ApiOwner= "XXXXXXXX",
ApiKey="XXXXXXXXXXXXXXXXXXXXXXXXX",
url = "https://dentalkart.vineretail.com/RestWS/api/eretail/v3/sku/inventoryStatus",
header= [#"Authorization" = ApiKey & ApiOwner,
#"Content-Type" = "application/x-www-form-urlencoded"],
RequestBody=
"{
""skuCodes"":""[]"",
""fromDate"":""18/06/2021 15:40:30"",
""toDate"":""23/06/2021 15:40:30"",
""pageNumber"":""1"",
""dropShipFlag"":""no"",
""locCode"":""
}",
webdata = Web.Contents(url, [Content = Text.ToBinary(RequestBody), Headers=header]),
response = Json.Document(webdata)
in
response
Any kind of help would really be appreciated !
Hi @akashgera ,
hard to tell without seeing the official API documentation.
What I find a bit surprising is that API Key and API owner are concatenated into one string without any separator or other structure. Is this really correct?
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@ImkeF Thanks for the response, please find below the web link of official API docs
I am not sure about the API Key and API owner whether it should be concatenated it or not, what I found on API docs, I have done the same thing, Also postman is giving me the data in response when I am passing all the parameters, For your reference I am attaching few screenshots from my system.
From Postman:
Please help !
I know this is a very old thread but I am trying to connect to API data of my own and hitting walls.
I am trying to connect to an ActiveCampaigns account data. I have tried the above method but this does not connect. I have tried the full web contents link above. And also tried adding the headers (not my speciallity)
Can the API key name be anything?
What you need to do is go into the Source Step in Power Query Editor or the Advaned Editor and find "Web.Contents" It should look like this:
Web.Contents("https://app.klipfolio.com/api/1.0/datasource-instances/{id}/data")
What you need to do is specify the API Key name AFTER the URL but before the close param. This is documented under the optional options of M Query here: https://docs.microsoft.com/en-us/powerquery-m/web-contents
Your final product should look like this:
Web.Contents("https://app.klipfolio.com/api/1.0/datasource-instances/{id}/data",[ApiKeyName="kf-api-key"])
Finally Return to "Credentials" Choose API Web and enter the API key in the box provided (if the wasn't already done).
I have the same issue connecting to Microsoft Cognitive Services Sentiment Analysis API.
Works no problem with the API key name and key hardcoded into the Web.Contents header section, but if I try to replace with the ApiKeyName option I get the same error. This option supposedly just inserts the specified header using the Credentials dialog inputs so I don't see why the result is different.
Surely this is a bug?
I know its a LONG time later, but here is how I solved this error:
API Method:
Steps:
I was then able to retrieve the API results and expand columns. AWESOME!
Hope this helps!
Works perfectly. Thanks!
This might look like a silly question for you, but how did you actually enter the API key and Value as a header?
I have this API from an Azure app service secured by Azure Active Directory, and I generated a key (client secret) from there. I am struggling to put that key into the request header when creating a Power BI report (Get Data from Web). I cannot find the API key name. I mean I got the actual API key but i do not have the API key name or can't seem to find it.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @ImkeF,
The .NET api itself, I created it without authentication, so at most, it is just anonymous authentication.
The actual authentication (Authentication/Authorization), I set it up in Azure when I published and registered my api there. The provider I set is Azure Active Directory. The registration allows you to generate a key for API access. This key I put as client secret for my api.
Hi @nnosaj. I'm fairly new at API calls and its been awhile since I worked on this project. I ended up writing a POST request in the Advanced Editor. Check this forum discussion between @ImkeF and myself. Might have some insights.
Good luck!
Thank you, @ericOnline! Yes, I might have to try the Advanced Editor. I find the UI is not as simple to use as it looks, when it comes to particular cases like this.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
90 | |
88 | |
83 | |
64 | |
49 |
User | Count |
---|---|
127 | |
108 | |
87 | |
70 | |
66 |