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!
Chris,
using Web.Contents in a dataflow I receive an error “Dataflow Contains Dynamic Datasource”. Might that be the same issue?
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?
@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?
I don’t see it either. I’ve been told that it has disappeared temporarily, but I don’t know why.
More details at the top of this blog. Unclear when it will come back for cloud data sources. https://powerbi.microsoft.com/en-us/blog/skip-test-connection-for-on-premises-and-cloud-data-sources/
Ok, at least I can stop searching for it now. Thanks guys 🙂 Guess I will try to de-parametrize web.contents.
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
Is it the tenants value that you need to make dynamic?
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
Hi Scott,
Wow! Never saw you’re reply! Thanks! I have solved it myself as well.
I’ve added a Uri.Parts-function to break away the dynamic part. This worked like a charm. Let me know if this sounds like less workarounds than you have and I can share the entire code if you’d like.
Regards,
Rob
Hi Scott,
Could you by any chance share the complete code? I am struggling to implement the proposed solution, however, I can see that it is exactly what I need!
Hi AggeVallentin and Scott,
I’ll share the code which is working fine now for the last 2 years. A big shout out to Gil Raviv who helped me get the final piece together! and it;s also his blog that helped create most, if not all, of the code overall.
The code goes all the way to the end, but I put comments in between, so there’s some explanation between the code. not sure if this helps with the readilibilty, but I thought it would be better than to have all the explanation at the end.
let
url = “https://auvikapi.eu2.my.auvik.com/v1/inventory/device/info?page[first]=300&tenants=xxxxxxxxxxxx&include=deviceDetail”,
Source = Json.Document(Web.Contents(url)),
iterations = Source[meta][totalPages],
UriParts = Uri.Parts(url), //this breaks apart the URL
Query = UriParts[Query], //this is the dynamic part of the URL, consisting, in this case, of tenants = xxxxxxxxxxx and include = deviceDetail
FnGetOnePage = //FnGetPage is the function that performs an import of a single page. The result contains only 2 fields: “Data” which is a list of records and “next” which contains the URL for the next page.
(url) as record =>
let
UriParts = Uri.Parts(url),
Source =
Json.Document(
Web.Contents(
“https://auvikapi.eu2.my.auvik.com/v1/inventory/device/info”,
[
Query = UriParts[Query]
]
)
),
data = try Source[data] otherwise null,
next = try Source[links][next] otherwise null,
res = [Data=data, Next=next]
in
res,
Ok, some explanation:
We need to write the Web.Contents by using Query in order to keep the core/base URL static for the Power BI Service to be able to refresh.The Query part is dynamic and in this part we reference the query part of the URL. The URL is the parameter for this function *
The line that starts with Json.Document( extracts the specific page with the input URL. The next line tries to access the field Source[data] which should contain the actual paginated data.
If the response doesn’t contains the page due to an error (usually when there is no more data), we assign null to the key Data. The next line gets the URL for the next page including its cursor string.
Because the Power BI Service needs a static url to be able to refresh, the first part, the core url is given as a hardcode value. This is ok, because the only part of the next url that is different/dynamic is the Query-part of the URL (see output of Uri.Parts(url) above)
Using the Web.Contents with Query combined with the Uri.Parts we have solved two issues: keeping the url as a dynamic parameter for the GetOnePageFunction AND keeping the core URL static for the scheduled refresh in the Power BI Service
Again, if there is no further page, we will get an error, and assign a null value to the key Next. Finally, we return a record of [Data=…, Next=…]
(The code continues from here)
GeneratedList = //this generates a list for each page, where we want to stop where there are no more pages. Here’s where the earlier defined ‘iteration’ comes into play.
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),
and then on with further transformations…. but that’s just the usual stuff.
Ok, final explanation:
*We initialize the List.Generate state with a record that contains a running index i=0 and a nested record res that holds the first record of FnGetOnePage. The next line defines the condition of the loop.
We will continue generating the list as long as the index i is smaller than the value in iterations, and as long as the field Data of record res is not null (When it’s null, there are no further pages to retrieve).
In our case we shouldn’t need this as we have the actual information on how many pages to iterate, but better safe than sorry
The next line defines the incremental step on the state of List.Generate. We increment the index by 1, and fetch the next record res that holds the new page in Data and the next URL with its cursor in Next.
Finally, we define the next item in the list, which is embodied in the field Data of record res
Now we have a list of pages which we can from here convert to a table and then further expand columns to retrieve the data we want
Let me know if it works out for you!
Cheers,
Rob van Zutphen
Hi AggeVallentin, I don’t have my code handy and I haven’t been using that API for a long while now, so I’d go with Rob’s code, as it’s still actively working for sure.
Rob, thanks for sharing!
This was perfect Rob, hugely appreciated. I did have to alter it as to include a subscription-key, but would never had made it without your help. Thank you so much!
For any others stumpling upon the issue, I managed to solve it by editing the following code, by adding an ApiKey/Header:
//CODE
let
ApiKey = “xxxxxxxxxxxxxxxx”,
url = “https://spor-uat.azure-api.net/pms/api/v2/MedicinalProductDefinition?_count=100&domain=xxxxxxxxx&ct=xxxxxxxxxxxxxx”,header= [#”Ocp-Apim-Subscription-Key” = ApiKey],
iterations = 1000,
UriParts = Uri.Parts(url), //this breaks apart the URL
Query = UriParts[Query], //this is the dynamic part of the URL, consisting, in this case, of tenants = xxxxxxxxxxx and include = deviceDetail
FnGetOnePage = //FnGetPage is the function that performs an import of a single page. The result contains only 2 fields: “Data” which is a list of records and “next” which contains the URL for the next page.
(url) as record =>
let
UriParts = Uri.Parts(url),
Source =
Json.Document(
Web.Contents(
“https://spor-uat.azure-api.net/pms/api/v2/MedicinalProductDefinition?”, [Query = UriParts[Query], Headers=header]
)
),
data = try Source[entry] otherwise null,
next = try Source[link]{0}[url] otherwise null,
res = [Data=data, Next=next]
in
res,
//CODE
Thank you sooo much Rob 🙂
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
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.
Exactly. I know the dev team are aware of the issue; I don’t know when it will be addressed though.
Hi Chris, is this possible now? (three and a half yrs later 🙂 )
No, sorry…
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.
I’m doing my best, I promise, but I don’t have a magic want I’m afraid 🙁
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.
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).
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?
No, sorry – when I get a chance I’ll check this out and try to find out what’s going on.
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.
What error are you getting exactly?
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?
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.
Hi Chris,
Thats a very interresting approach. Any thoughts on how to make it work (enable Power BI Services Schedule Refresh) when it comes to use the function Web.BrowserContents? It didn’t have this paramaters that allows to make this work around, which limits a lot the usage of it for Web Scrapping on Power BI. I have some Reports on which i scrap data from multiple websites pages but i can only refresh it on my PC.
Hi Chris,
I have a problem using YahooFinance API V8. The challenge here is that there is no “core” url that give no errors.
Here is my code that works perfectly in Power BI Desktop but fails when published:
= (StockSymbol as text) => let
Origen = Json.Document(Web.Contents(“https://query1.finance.yahoo.com”,
[RelativePath =”/v8/finance/chart/”& StockSymbol &”?range=5y&interval=1d”])
As you can see, I used the relativePath in order to put the dynamic part, which is the StockSymbol before the “?”. The problem is that the web page “https://query1.finance.yahoo.com” throws an error. The same if I inculde the other parts, until I add manually a StockSymbol (let´s say TS for example).
The issue with that, is that the query part is would be “range=5y&interval=1d” , when in reality I want it to be the StockSymbol.
I tried with “Skip Test Connection”, but still no success. The error message is the following: “Information is needed in order to combine data. Please specify a privacy level for each data source.;Information about a data source is required.”
Could you or anyone please help me?