Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ibutur
Frequent Visitor

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

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?

24 REPLIES 24
ImkeF
Super User
Super User

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.

akashgera_0-1628169087361.png

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

akashgera_1-1628169333267.png

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 

ImkeF
Super User
Super User

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

ImkeF
Super User
Super User

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 :-

akashgera_0-1627111156526.pngakashgera_1-1627111185726.pngakashgera_2-1627111245134.pngakashgera_3-1627111249240.png

 

akashgera
Helper I
Helper I

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

 

 

akashgera_0-1626959494830.png

 


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

Vinculum 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.

 

akashgera_1-1627045322167.png

From Postman:

akashgera_2-1627045535169.png

 

Please help !

rob_tucker_168
New Member

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? 

power automate.png

mmarbut
Frequent Visitor

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).

Anonymous
Not applicable

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

  • GET

Steps:

  • Click Get Data
  • Click Web
  • Click Advanced
  • Enter entire query string with parameters
  • Enter API Key and Value as a header
  • Click Connect
  • HERES THE SECRET: Select "Anonymous"
    • It was intuitive for me to want to click "API Key". But this is what resulted in the error.

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. 

What kind of authentication does your app allow?

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

nnosaj
Frequent Visitor

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.