Using The “Skip Test Connection” Option For Power BI Data Sources To Avoid Scheduled Refresh Failures

One of the most popular blog posts I have written in recent years is this post on how the RelativePath and Query options for the Web.Contents() M function can be used to allow datasets that use web services as data sources to refresh successfully in the Power BI Service. It’s very frustrating when you write some complex M code in Power BI Desktop and find that your dataset can’t be refreshed after it has been published! While, at the time of writing, this problem is still present, yesterday’s announcement of the new “Skip Test Connection” option for data sources in the Power BI Service does mean there are more scenarios where your datasets can be made to refresh.

As I mentioned in that original post, the trick of using the RelativePath and Query options with Web.Contents() only works if the first parameter passed to Web.Contents() is a url that itself can be called on its own without an error. For example, the following expression:

Web.Contents(
	"http://www.myservice.com",
	[RelativePath="hello",
	 Query=[q="SearchTerm"]
	])

…will only refresh successfully in the Power BI Service if http://www.myservice.com can be reached as a url on its own without returning an error. I believe this is because this url is used when the Power BI Service is preparing to run the query to check whether the credentials it has stored are valid.  As the blog post says there is a workaround where dummy query parameter values can be used in that first parameter, but this won’t work in all cases. Instead, now you can turn on the “Skip Test Connection” option on the data source in the Power BI Service and the dataset will refresh even if http://www.myservice.com on its own returns an error.

Similarly this new option helps when you are using API keys passed in through query parameters for authentication. In Power BI Desktop and Excel Get&Transform/Power Query, you have the option to use the Web API authentication type to store sensitive API keys that need to be passed through url parameters securely rather than embed them in your M code. Unfortunately this is not supported in the Power BI Service yet and (as I found out on a recent consultancy engagement) there are other complications with using Anonymous authentication. For example, say you need to call a web service with an M expression something like this:

Web.Contents("http://www.myservice.com?key=password123")

…where the key query parameter contains an API key used for authentication. If you set this data source to use Anonymous authentication in Power BI Desktop or Excel it will refresh with no problems. However, after you publish to the Power BI Service you will not be able to refresh your dataset unless you turn on the “Skip Test Connection”, because the Power BI Service apparently strips out any query parameters from the url when it is testing the connection. In this case, if it strips out the key parameter, then obviously the call will fail because it has removed the means of authentication.

This new option does not mean every dataset can now be refreshed. As far as I can see, if you dynamically generate a url for use with Web.Contents() and do not use the RelativePath and/or Query options you will not be able refresh your dataset because the Power BI Service will not be able to analyse your code to discover what urls are present in it. I suspect that if you see the “Some data sources may not be listed because of hand-authored queries” warning in the Data Source Settings dialog in Power BI Desktop (which I blogged about here) then you will not be able to refresh your dataset in the Power BI Service, but I need to do more testing to be sure.

As always, if you test this yourself please let me know about any interesting things you find by leaving a comment!

8 responses

  1. Chris,

    using Web.Contents in a dataflow I receive an error “Dataflow Contains Dynamic Datasource”. Might that be the same issue?

  2. Pingback: Using The "Skip Test Connection" Option For Power BI Data Sources To Avoid Scheduled Refresh Failures | Pardaan.com

  3. Hi Chris,
    I haven’t been able to find Skip test connection option in Power BI service on the dataset or anywhere else? Can you help out finding it, please?

  4. @Matija – strange, because i had this option last week (probably when they rolled out on May the 25.) but now it’s gone. Was this feature reverted?

  5. i Chris, thanks so much for your blogs! Like many others I too ran into the refreshing issue and like you say at the end of this blog, when you’re using a dynamically generated url, then you still need the Relative Path and/or Query method. I have used your advice, and it works!

    However… my url isn’t dynamic anymore. I’m hoping it’s just a small adjustment, but I’ve just spend hours and hours on end finding out what the problem is, but I’m at a dead end. So… I’m hoping you can help me.

    This is the code I’m using (thanks to Gil Raviv’s very usefull blog about cursor based pagination https://datachant.com/2016/06/27/cursor-based-pagination-power-query/)

    let
    url = “https://auvikapi.eu2.my.auvik.com/v1/inventory/device/detail?tenants=xxxxxxxxx”,
    Source = Json.Document(Web.Contents(url)),
    iterations = Source[meta][totalPages],

    FnGetOnePage = (url) as record =>
    let
    Source = Json.Document(Web.Contents(url)),
    data = try Source[data] otherwise null,
    next = try Source[links][next] otherwise null,
    res = [Data=data, Next=next]
    in
    res,

    GeneratedList =
    List.Generate(
    ()=>[i=0, res = FnGetOnePage(url)],
    each [i]<iterations and [res][Data]null,
    each [i=[i]+1, res = FnGetOnePage([res][Next])],
    each [res][Data]),
    #”Converted to Table” = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
    in
    #”Converted to Table”

    Where many blogs explain how to set up an iteration over multiple webpages, the api I’m using gives a new url for each page. Gil Raviv’s blog explains in detail how to handle this, and hence my code is formed. But… to be able to get this query refreshed in the Power BI Service I have read your blog and implemented your method in the code. See below:

    let
    url = “https://auvikapi.eu2.my.auvik.com/v1/inventory/device/detail?tenants=xxxxxxxxx”,
    Source = Json.Document(Web.Contents(url)),
    iterations = Source[meta][totalPages],

    FnGetOnePage = (url) as record =>
    let
    Source = Json.Document(Web.Contents(
    “https://auvikapi.eu2.my.auvik.com”,
    [
    RelativePath=”v1/inventory/device/info”,
    Query=
    [
    tenants=”391134359548238589″,
    ]
    ]
    )),
    data = try Source[data] otherwise null,
    next = try Source[links][next] otherwise null,
    res = [Data=data, Next=next]
    in
    res,

    GeneratedList =
    List.Generate(
    ()=>[i=0, res = FnGetOnePage(url)],
    each [i]<iterations and [res][Data]null,
    each [i=[i]+1, res = FnGetOnePage([res][Next])],
    each [res][Data]),
    #”Converted to Table” = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
    in
    #”Converted to Table”

    Basically I altered only the line below the FnGetOnePage, just after the 2nd “let-statement”.
    Although my refreshing issue is solved, the URL is no longer dynamic.
    Can you (or anyone readintg this blog) point in me in the right direction on how to solve this?
    How can i keep it dynamic/parametrized, while still keeping the first part static for the static analysis which Power BI performs…?

    Really hope you can help!

    Regards,

    Rob van Zutphen

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: