Currently, there are some challenges on Power BI service when trying to Schedule Refresh from a Web API data source that uses an API key for authentication. On this post, I will talk about a way to work around this problem by using an Azure Functions Proxy. The proxy will be used the override the request to the Web API and successfully use anonymous authentication.
Scenario overview
I have some reports on Power BI that get the data from WorkflowMax API, they have been published on Power BI service and embedded on SharePoint Online. These reports have the correct data when they get published to Power BI service, but the dataset can’t be refreshed after being published, either by using the Refresh Now or Schedule Refresh options. I get an error when trying to configure the dataset credentials to connect to WorkflowMax API due to the use of a key in the URL.
Problem
Steps to reproduce
Step 1: From Power BI desktop use the get data from Web to connect to WorkflowMax API.
WorkflowMax API requires AccountKey and ApiKey for Authentication which need to be provided on the URL as parameters on the following format:
https://api.workflowmax.com/ [yourPath]? accountKey=[yourAccountKey]&apiKey=[yourAPIKey]
Step 2: Clean and format the dataset, create report and publish it to Power BI Service.
Note: This is not covered on this blog.
Step 3: On Power BI service click on the three dots of your dataset and select Schedule Refresh, under Data source credentials following error appears:
If you edit credentials and try to log in using anonymous access, you will get the error “Failed to update data source credentials: The credentials provided for the Web source are invalid” because the API key is missing.
Solution using an Azure Functions Proxy
A possible solution is to create an Azure Function Proxy to override the request to WorkflowMax API, by using this approach the Power BI dataset will connect to the proxy instead of the WorkflowMax API.
Note: You will need an Azure subscription for this solution
Steps to create the Azure Function Proxy
Step 1. Log into your Azure Portal and create an Azure Function.
Step 2. Expand your Azure Function and create a Proxy with the following configuration for connecting to the WorkflowMax API.
After the creation you will get the proxy URL that is going to be called instead of the API.
https://sample-wfm-proxy.azurewebsites.net/{apiKey}/{accountKey}/{*restOfPath}
Steps to update the data source from API to the Proxy
Step 1. On your Power BI solution, open the Power Query Editor that lists all the datasets and add apiKey, apiAccount and fromDate as parameters.
Step 2. Add toDate as empty query and set it to current date.
Step 3. Change source of all the dataset sources from calling the API to calling the Azure Function Proxy.
From:
To:
Step 4. Click done, use anonymous authentication to connect to the Azure Function Proxy if prompted.
Step 5. Publish your report to Power BI service and Schedule Refresh on the dataset, you will get the same error as before, but now you will be able to log in with anonymous access.
Login with anonymous access.
Step 6. Now, you will be able to schedule the refresh.
Conclusion
By using an Azure Function Proxy it is possible to bypass the Power BI restriction of not being able to do anonymous authentication on Web data sources that require an API key.