cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rdubs
Frequent Visitor

API authentication error on scheduled refresh!

I have a couple of API feeds which I connected through the web connector. These require an api key for authentication, I added this in the header using the advanced section.

 

The data refresh works fine on the desktop version but I have tried to set up a scheduled refresh and I am getting an authentication error. It seems as though it isn't pulling through the api key in the header:

 

error message.png

 

I feel as though M might be the answer but i have little (to no) experience with M so any help would be appreciated!

1 ACCEPTED SOLUTION

Try this, substituting in your api key, and see if you see the same error

 

let
vBaseUrl = "https://api.projectmanager.com",
vPath = "api/v1/projects.json",
vHeaders = [ #"apikey"="this is where i put my key" ],
Source = Web.Contents ( vBaseUrl, [RelativePath=vPath, Headers=vHeaders] ),
Json = Json.Document ( Source )
in
Json

View solution in original post

10 REPLIES 10
blopez11
Super User
Super User

For web service APIs, what works in the Desktop doesn't always work in the service

It is likely due to anonymous authentication not being supported for the URL in the error message

It would be helpful to see the M code used in your query, you can blur out the API key

 

rdubs
Frequent Visitor

Hi @blopez11 

 

thanks for the speedy response!

 

This is the M code:

 

let
Source = Json.Document(Web.Contents("https://api.projectmanager.com/api/v1/projects.json", [Headers=[apikey="this is where i put my key"]]))
in
Source

 

 

Try this, substituting in your api key, and see if you see the same error

 

let
vBaseUrl = "https://api.projectmanager.com",
vPath = "api/v1/projects.json",
vHeaders = [ #"apikey"="this is where i put my key" ],
Source = Web.Contents ( vBaseUrl, [RelativePath=vPath, Headers=vHeaders] ),
Json = Json.Document ( Source )
in
Json

the relative path setting is critical and solve issues with API when have cradentials indicate a failure on refresh.

see also Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code 

I tried this exactly same approach and my datasets seems to be working correctly as webapis, but I still get the credential warnings on the powerbi service when I try to connect the gateway even using anonymous authentication.

Any suggestions?

antik
Regular Visitor

Did you try using the "Skip test connection" option? Using this option is nicely explained in this blog post: https://blog.crossjoin.co.uk/2019/04/25/skip-test-connection-power-bi-refresh-failures/

rdubs
Frequent Visitor

It worked at first @blopez11  and let me put details into the 'basic' authentication section, but when the scheduled refresh came through it showed me this:

 

Another error.png

 

Is there a way of just including the api key into the URL itself?

Try using anonymous authentication, I thought that was the method you were using from the initial comment.

If you need basic autentication, then that requires a username and password

For basic, sometimes I see vendors have some kind of combo value for the password (i.e. api user password + key)

 

I guess I am not sure of the authentication your API requires

 

Kuda
Frequent Visitor

@blopez11  thanks a lot.

rdubs
Frequent Visitor

Great, thanks @blopez11 

 

Worked a treat!

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors