Web API connection

[Sam_Jain]

Hello Commnuity,

Firstly apologies for asking this question again, the first one got marked and accepted as solution by default (my fault here for not repsonding back on time) However, my issue still persists and any help would be greatly appreciated. 

I'm trying to establish an API connection with data source as 'Web' on PowerBI to say xyz.com. Now, this xyz.com has plug and play dashboards, but what i need to do is establish a direct connection so i can call for any/all data present directly. As opposed to exporting to csv from the site and then using that as the source.

I keep running into the error message which states "An API key value isn't specified". I was able to successfully test it on Postman i specify the key name as x-api-key and then the value is the actual key and chose the add to header option. I only need the GET method and specify the date range along with source id - say 'Instagram'. To retrieve all social data present on the site on the specified date range.

With PowerBI however, i'm struggling, i can't seem to get past the first step itself to establish connection I think once am able to get past that i could play around and try to retrieve some data. If anyone has any inputs it'd help me a whole lot, but from the links that i've gone through they all seem to use a different form of establishing connection(direct web api/or api with Oauth) and none for what i have which is just the API key, the authorization step on BI is where it fails.

Thank you so much in advance for any help!!

@PhilipTreacy 

[parry2k]

Well done @Sam_Jain !!

What help do you need? How I can help?

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) 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!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[Sam_Jain]

Update: I was able to establish the connection by passing the api key name and key value in the web option and then choosing anonymous while signing in. 

My next step here is to write the code to include the necessary metrics and a scheduled refresh. Any links towards those.

Thank you all for your help! 

[Peter_Jeyaraj_I]

Hi Sam, 

Can you please share how you managed to authenticate.. Still i got stuck to establish the connection. 

See the below error. but everything works fine in Postman

[Sam_Jain]

Hey @Peter_Jeyaraj_I  Sorry for the delay in response.

You can overcome that error by entering the api key name as well as value on the Get Data->Web->Advanced

Enter the URL and under the Request Header Parameters enter the key name alongside with the Key value. 

And then choose Anyonymous under authentication.

Tio note - In this case the value was passed via header so if that's how it works for you on Postman as well then this should be the solution.

Again apologies for the delay.

Thanks.

[Peter_Jeyaraj_I]

Hi @Sam_Jain 

Yes, it was working fine when i add "Bearer" in front of the key value.. anyhow, thank you

[Sam_Jain]

@ImkeF @akashgera  Hello, saw your replies on a similar query. Wondering if you have any inputs on this one. Any help would be very appreciated.

[ImkeF · Accepted solution]

Hi @Sam_Jain ,
hard to tell, quickly went through all the other answers here without checking all the links and details.
So I assume that you're using anonymus authentication with the key?
If that's not working, I would recommend to compare the generated queries by Postman and PBI using Fiddler or any other tool.
See where the queries differ and try to adjust accordingly.

[parry2k]

@Sam_Jain very hard to figure out what is happening. The only way is if you can share the API details, maybe it is not practical, maybe there is a test environment and API Key which you can share to try it, not sure what else to say.

[mh2587]

referring to the below blog
https://blog.crossjoin.co.uk/2014/03/26/working-with-web-services-in-power-query/

hope it will help you

[Sam_Jain]

Hello @mh2587  Tried the method from the blog. However it results in the same error as "Expression error: Access to the resource is forbidden"

Thanks.

[parry2k]

@Sam_Jain check this video, it contains everything you need.

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) 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!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Sam_Jain]

Hello @parry2k 

While researching upon the topic i had come across this video. Sadly, i can't seem to take much out of it.

I tried the section where i include the API key name in the M code itslef and when asked for authentication i entered the key value, this time it let me through without the "an API key value needs to be specified error" which occurs when i use the direct web connector. However as soon as it said connecting to xyz.com i got a second error which reads "Expression error: access to the resource is forbidden" i have tried clearing the permissions and it still is on the same error.

The Mcode is as follows:

Source = Web.Contents("https://api.xyz.com/page-metrics/get",[ApiKeyName="x_api_key"])

Also, it is a static key that i have!

Any inputs to overcome this?

Thanks

[parry2k]

@Sam_Jain it is very hard to provide details without looking at the issue.

[Sam_Jain]

Hello @parry2k 

Can you please tell what kind of details i can share here, while respecting the confidentiaity of the process.

Thanks.