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!

27 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

    • Hi Rob, I know this post is several months old and I don’t know if you ever solved your problem, but thought I’d respond for posterity. I just followed on your journey with Auvik and eventually got there. It was a frustrating experience, so I hope this helps you or a future visitor. The same principles can be used for other APIs, but I will specifically address Auvik since that’s what we’re both working on.

      There are a few problems to address.

      #1. In your function, your parameters for Web.Contents are static:

      Source = Json.Document(Web.Contents(“https://auvikapi.eu2.my.auvik.com”,
      [RelativePath=”v1/inventory/device/info”,
      Query=[tenants=”xxxxxxxxxxxxxxxxxxxx″,]])),

      So, you’re going to iterate over the same page again and again every time the function is called. That’s why you’re getting the same page data multiple times. You need to use a variable, but this is where we get into trouble with Power BI Service, so…

      #2. You can’t just use a straight-up variable as the parameter for the function, as it will fail in Power BI service. (this may or may not be true but I I’m burnt out on this and don’t care to investigate further now that it works). What I did was set the url passed to Web.Contents as most of the url, then used a variable passed to the function to specfiy the RelativePath. In order to get the proper text for the RelativePath from the function, I used Text.AfterDelimiter to extract it from the “next” record in the API metadata. I then also had to “hide” the function from Power BI service by creating a function query that is just the function, like so:

      let
      Source = (Path) =>
      let
      Source = Json.Document(Web.Contents(“https://auvikapi.us1.my.auvik.com/v1/inventory/device”,[RelativePath=Path])),
      data = try Source[data] otherwise null,
      next = try Source[links][next] otherwise null,
      afterstring = try Text.AfterDelimiter(next,”/device/”) otherwise null,
      result = [Data=data, Next=next, AfterString=afterstring]
      in
      result
      in
      Source

      Then.at the function call, instead of passing the url, I passed the RelativePath. In the Initial parameter of the List.Generate function, this is simply “info”. In the “next” parameter of the List.Generate function, the string retrieved using Text.AfterDelimiter is passed. AuvikPaging is what I named the function query.

      GeneratedList =
      List.Generate(
      ()=> [i=0, result = AuvikPaging(“info”)],
      each [i] < CountOfPages,
      each [i=[i]+1, result = AuvikPaging([result][AfterString])],
      each [result][Data]),

      #3. Once you've got your query working in Power BI Desktop, you may need to remove the credentials from the Auvik data source so you can specify them in Power BI service. Then, you append ?skipTestConnection=true to the end of the Power BI Service login URL, login to Power BI Service, go to your data set and input the credentials there. The "Skip Test Connection" option will be available and you can set it.

      It appears to be working for me at the moment, but it does appear to rely on two workarounds (hiding Web.Contents in a function query and appending the hidden parameter ?skipTestConnection=true to the Power BI url) that may or may not last as viable options.

      Best,
      Scott

  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.

  9. Is there an Idea posted on the PBI Forum to allow datasets with web services as data sources to refresh in the Service? This is causing a problem for our TeamCity data, and the fixes described in your posting didn’t work for us.

  10. Good news: “Skip Test Connection” has now been re-enabled for OData and web data sources that do not use a gateway (it was re-enabled some time ago for gateways).

  11. Hi Chris,
    The Skip Test Connection work-around does not seem to be working for a POST request i am making to a rest service. I am still getting the following error when trying to set up the dataset to refresh through the gateway:
    You can’t schedule refresh for this dataset because the following data sources currently don’t support refresh:
    Data source for “SCCR – Names”

    Discover Data Sources
    Query contains unsupported function. Function name: Web.Contents

    Any ideas?

  12. Any update on this issue. I have a report which I pull weather forecast data from the National weather Service for a list of store locations that I can update great from Desktop but cannot get to update at all from the service. I also however cannot find anything about how to turn on skip test for an existing web api connection.

  13. Pingback: Historical Stock Price Function in Power Query - Power BI Tips and Tricks

  14. Hello Chris, thanks for your great post. I have question regarding the url and relative path used. Can this be variable as well? I use:
    Web.Contents(
    “VarURL”,
    [RelativePath=”VarRelativePath”,
    Query=[q=”SearchTerm”]
    ])
    However, this does work when working in desktop, but doesn’t when uploading to Online. Do you know a workaround?

  15. Hi Chris

    Request you to help me with the below

    let
    Source = Json.Document(Web.Contents(“https://ea.vmware.com/blackouts.json”, [Headers=[#”X-User-Login”=”kiranv”, #”X-User-Token”=”MxiRsqCeRxM5G2scxwyV”]])),
    #”Converted to Table” = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #”Expanded Column1″ = Table.ExpandRecordColumn(#”Converted to Table”, “Column1”, {“id”, “name”, “rule”, “indexorder”, “createdUser”, “updatedUser”, “created_at”, “updated_at”, “url”, “active”}, {“id”, “name”, “rule”, “indexorder”, “createdUser”, “updatedUser”, “created_at”, “updated_at”, “url”, “active”}),
    #”Expanded id” = Table.ExpandRecordColumn(#”Expanded Column1″, “id”, {“$oid”}, {“$oid”}),
    #”Added Custom” = Table.AddColumn(#”Expanded id”, “Mgr1”, each Json.Document(
    Web.Contents(
    Text.Combine({“https://itopstools.vmware.com/api/user_org?username=”,[createdUser]})
    )
    )),

    I’m unable to fix the call in the web.contents above as per your recommendation, web refresh is failing each time.

  16. Pingback: Como obter dados de APIs e atualizar automaticamente - Aprenda Power BI

Leave a Reply

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

%d bloggers like this: