Use missing Skip Test Connection for Scheduled Refresh in Power BI

In my previous post I showed a method for retrieving data from a table storage using SAS-tokens. Now when you want to enable Scheduled Refresh you will run into the following problem

Error: Query contains unsupported function. Function name: Web.Contents

Meaning the Power BI Service does not support calls to the Web.Contents-function if it does not know what URL to call to. And this is because it wants you to explicitly agree that the dataset will call a certain URL.

Should be easy to fix, right? Moving the URL declaration directly into the Web.Contents-call.

If we now verify the credentials as Anonymous Power BI will try to connect to the URL without the query. For our case this would be https://storagename.table.core.windows.net/tablename, this URL without the query is an invalid request and will return an ResourceNotFound 404 HTTP status code. For it to return a valid 200 HTTP status code we will need the URL with the query string.

Skip test connection

After some digging around I found that in April Microsoft introduced the Skip test connection checkbox. Which would not require the URL to return a valid status code in order to confirm credentials. Sadly the feature was quickly removed from the service due to a ‘few issues’.

I suspected that Microsoft is just hiding this feature in the user interface, and that it is still enabled on the backend. So my starting point was to look at the network requests it did to the server when one verifies a credential. And surely I could confirm my suspicion. Each time that you verify credentials skipTestConnection is passed in the request payload.

skipTestConnection is still passed in the request payload

So, how can we utilize this? We can replay this request while we change the skipTestConnection. Here is how to do this:

1. Go to your Data source credential settings of your dataset
2. Open the Chrome DevTools (CTRL+Shift+I) and go to the Network-tab
3. And click Sign-in using Anonymous authentication and your privacy level
4. Now you see a new PUT request, copy this one to Powershell using the Copy to Powershell option in the context menu
5. Open Powershell and paste it in, and don’t forget to change the skipTestConnection from false to true

Thats it! Now you should be able to setup your scheduled refresh again. Hopefully Microsoft will re-enable this feature in the future. If this helped you don’t forget to leave some claps!