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!

16 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

  6. Hi Chris,

    the tenant will always be the same, but an extra part gets added to the URL, which is a ‘random’ string and this part needs to be dynamic. This string comes from the ‘page after’-part of the api-response.
    Basically, every api-response gives one page which contains the data (see Source[data] in code above), and a link to the ‘next url’ (see Source[links][next]). This next url is the new input to be iterated in the Function GetOnePage and List.Generate (the next url in itself contains again a page with data and a link to the next url) And so on and so on. This loop needs to continue untill all different url’s and its data have been added to the query.
    The code, as it was, works fine, but the refresh wasn’t possible. I then changed the code following your blog, but now the parameter of the function (url) isn’t a parameter anymore, as I’ve replaced it with your code (which now is not just the url but also include “Json.document” and “web.contents”. Without the Web.Contents I cannot use relative path to break apart the url into a static part and a dynamic part…but wíth it I don’t have a url as input but the whole code as input…

    So I somehow need to keep the parameter intact, but in such a way that the Power BI Service reads a static URL….I really don’t know what I need to adjust to get the right results…..

    Any thoughts?

    Many thanks in advance,

    Rob

  7. Hey Chris,

    Did you get any info from Microsoft as to when or if they’re going to add support to securely store the API keys in the Power BI service the same way they do it in Power BI Desktop? It’s not the most secure design to be storing the keys directly in the M script, so I’d hope they’re planning to address that.

    Brad

    • Hi brad,

      (sorry to interupt)

      If you don’t add your keys in the header in the M-code, but just supply them in the Power BI credential/authentication, then you need to also supply them in the Power BI Service (dataset – setting – edit credentials). This way they’re not in the M-script.
      (or am I misunderstanding?)

      Greets Rob

      • Hey Rob, that’s how I tried to implement it, but the Power BI service doesn’t give you a way to use key authentication for web API’s. Your only options are Basic, Anonymous, and OAuth…none of which will work in this scenario.

  8. Hi Chris, any chance that now you are “inside the tent” you can direct the team to “make it so” and get this issue fixed properly?

    Fundamentally it baffles everyone that a working Desktop refresh doesn’t work in the cloud or gateway. Its a bit farcical that you can then use your workaround to defeat that restriction. Sometimes.

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: