If you are experiencing a refresh failure in Power BI involving a web API as a data source, you should troubleshoot the issue in the following sequence:
1. API Authentication and Connectivity
The authentication method of the API (that is, whether it is an API key, OAuth, Basic Auth, etc.) should now be known. Power BI should have valid authentication stored in Data Source Settings in Power BI Service.
Refresh the token if the access token for OAuth appears to expire.
On Postman or through Power Query Editor, ensure to test the API (other options are Web.Contents()) that makes a successful request to the API other than Power BI.
2. Optimizing Query Performance and Data Handling
In case the API imposes rate limits, data could be obtained in smaller batches using some form of pagination such as List.Generate() or specify the RelativePath on the Web.Contents().
Ensure that the Power BI Service works with the API's query structure (e.g., POST requests are not supported in scheduled refresh).
Use Power Query to limit the size of the data by filtering out unwanted fields or rows before loading it into Power BI.
3. Fix Refresh Errors for Power BI Service
Visit Dataset Settings → Refresh History to get clues via error messages.
Where "Access to the resource is forbidden," check if the API permissions are okay, then make sure the On-Premises Data Gateway is being used when necessary.
Proceed to increase the timeout setting with Web.Contents([Timeout=#duration(0,0,5,0)]) if you get 'Timeout expired.'
If the API URL is dynamically built, refrain from using the Text.From() inside the Web. The contents () function is not honored by Power BI Service for any dynamically created URLs.
