Power Bi rest API gives errors on Power BI service when refreshed

[Anonymous]

Hi,

After a lot of researching I got stuck here. I hope there is someone that could help me out. 

I managed to connect Power BI desktop to an REST API with a blank query. I use a accestoken to login to the data source. 

Verification method is anonymous, since this is the only posibility with Web.Contents as far as I know. 

But this works perfectly in Power Bi and I can refresh it with no problems. 

But when I publish the rapport to the Power BI service and I try to refresh the data set I run into some issues. 

Even when I say "Skip test connection". The error code I then get is:

"We cannot convert the value 403 to type list" 

I'm not sure if it is caused or related too, but the organizations uses its own IP-adres and only that ip-adress is whitelisted. I noticed that the Power BI services tries to login from a microsoft IP adres. Which makes sense but I'm not sure if it is possible and/or needed to also put the organizations ip adres in the query. 

I hope that someone is able to help me out,

Thanks a lot in advance, 

[Anonymous]

Hi @Anonymous ，

Could you please provide the applied codes(mask the sensitive data) in Advanced Editor if it is possible? Also please provide some sample data in your model and the related screenshot with error message in Power BI Service in order to make troubleshooting. Thank you.

In addition, please review the content in the following links and check whether they can help you resolve the problem.

Power BI embed error 403 forbidden error from server

REST API Troubleshooting 

Best Regards

[Anonymous]

Hi @Anonymous,

Thank you for your help. 

So for the script I used I got inspiration from the following link:https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-in-power-query-and-power-bi-m-code/ 

The scripts to get the data in powerbi are show in following screenshots: 

This works perfectly in Power Bi Desktop but when published to the Power Bi service it gives an error when the dataset is refreshed. The first error I get can be seen in following picture: 

When I check the box "skip test connection" as was advised here: https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-in-power-query-and-power-bi-m-code/

This error dissapears but then I get the following error message: 

The data that I try to retrieve can be seen as a regular table like this:  

company name                sales                            country                             status

a                                        500                              USA                                  to be determined

b                                         400                           DE                                       done

c                                         700                           ITA                                       in progress

Sorry for all the edits but it was the only way to poste a reply sadly (something wrong with the html or something) 

Thanks Again! 

[Anonymous]

Hi @Anonymous ,

Please check and validate whether it face any following situation:

A fiddler capture may be required to investigate further. There could be several reasons for a 403 error.

• The user has exceeded the amount of embed token that can be generated on a shared capacity. Purchase Azure capacities to generate embed tokens and assign the workspace to that capacity. See Create Power BI Embedded capacity in the Azure portal.
• The Azure AD auth token expired.
• The authenticated user isn't a member of the group (workspace).
• The authenticated user isn't an admin of the group (workspace).
• The authenticated user doesn't have permissions. Permissions can be updated using refreshUserPermissions API
• The authorization header may not be listed correctly. Make sure there are no typos.

Best Regards

[Anonymous]

Hi @Anonymous 

Thank you for your reply! 

I'm already working on this list. One question just for clarification, do you mean with the authenticated user, the account that is used to login on the API (In the script, like the username and passoword) or the account that is used to login into the workspace which is a outlook account. 

Thanks a lot! 

Alex