Forum Discussion
VSTS odata refresh issue
We are reading data from the VSTS odata source. Because the queries return too much data, we create a table of
date values, then use these date values to limit the scope of the query. Everything works perfectly well in Power BI
desktop, but once published, it will not refresh.
This query generates a table of date values and issues a single VSTS.Feed call, ignoring the date table values completely.
It refreshes with no issues.
let
// Here are some Date functions
now = DateTimeZone.UtcNow(),
table = Table.FromRows({{0},{-30},{-60},{-90},{-120},{-150},{-180}},{"DaysBack"}),
Stop = Table.AddColumn(table, "Stop", each Date.ToText(Date.From(Date.AddDays(now,[DaysBack])),"yyyyMMdd")),
Start = Table.AddColumn(Stop, "Start", each Date.ToText(Date.From(Date.AddDays(now,[DaysBack]-29)),"yyyyMMdd")),
// Here is a call to a VSTS data source
URL = "https://OurAccount.analytics.visualstudio.com/DefaultCollection/OurProject/_odata/WorkItemSnapshot?$select=DateSK,WorkItemId,State,WorkItemType&$filter=WorkItemType eq 'Bug' and DateSK gt 20180521",
VSTS = VSTS.Feed(URL)
in
VSTS
However, if we actually use the date table and run multiple queries, it won't refresh it at all after it is published.
let
// Here are some Date functions
now = DateTimeZone.UtcNow(),
table = Table.FromRows({{0},{-30},{-60},{-90},{-120},{-150},{-180}},{"DaysBack"}),
Stop = Table.AddColumn(table, "Stop", each Date.ToText(Date.From(Date.AddDays(now,[DaysBack])),"yyyyMMdd")),
Start = Table.AddColumn(Stop, "Start", each Date.ToText(Date.From(Date.AddDays(now,[DaysBack]-29)),"yyyyMMdd")),
//Combine the URL with the contents of the data function.
BaseURL = "https://OurAccount.analytics.visualstudio.com/DefaultCollection/OurProject/_odata/WorkItemSnapshot?$select=DateSK,WorkItemId,State,WorkItemType&$filter=WorkItemType eq 'Bug' ",
AddURLColumn = Table.AddColumn(Start, "URL", each BaseURL & " and DateSK ge " & [Start] & " and DateSK le " & [Stop]),
AddSource = Table.AddColumn (AddURLColumn, "Source", each VSTS.Feed([URL]))
in
AddSource
There are two errors that occur, with variations of the above query. One error given is "You can't schedule refresh for this dataset because one or more sources currently don't support refresh." The other is:
Please try again later or contact support. If you contact support, please provide these details.
8 Replies
One bit of information that I find interesting is that if I change VSTS.Feed([URL]) to VSTS.Contents([URL]), the dataset will refresh
with no issues. However, the values returned by VSTS.Feed() are binary and much more difficult to transform into useful rows and
columns.
Hi,
Looks like you are trying to view work item snapshots monthly. Have you tried new Analytics views? It has a feature to return history (which is what workitemsnapshot is) at monthly intervals.
The data sources shown are from the analytics view feature. We are accessing it via the odata feed using the VSTS.Feed() method to get complete work item histories over a 6 month period. The reason for multiple queries is because a single query with 6 months of data returns >300K rows.
As best I can tell, the problem seems to be an issue in the VSTS.Feed() method itself.
- A single call to VSTS.Feed() in a query works properly in apps.powerbi.com.
- Multiple, sequential calls to VSTS.Feed() in a query work
- Multiple calls to VSTS.Feed() in an each loop cannot be refreshed in apps.powerbi.com. They fail as shown
in the original message. - Using VSTS.Contents() in an each loop refreshs without issue. However, this method is deprecated, doesn't return
all of the data (it provides a continuation token), and also returns a binary object which is harder to process. - Using VSTS.AccountContents()--which replaces VSTS.Contents()--fails the same way that VSTS.Feed() does.
- Using OData.Feed()--which was replaced by VSTS.Feed()--fails the same way that VSTS.Feed() does. It also
doesn't return all of the data, but instead returns a continuation token.
Given the number of options provided to run a query, it would be nice if one of them worked.
Hi,
Some more information :
PowerBI has a limitation where it will not allow user to modify the URL due to the way security scopes work.
The following two articles go into detail of why this happens and how to work around it using query parameters and RelativePath parameter - which VSTS.AccountContents also allows you to pass.
https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-in-power-query-and-power-bi-m-code/
and
https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power-bi/Thank you for the assistance arsaveli. I posted this as a bug here, and got the response you just passed on. However, there
are still multiple problems to solve. First, the blog post does not show anything about VSTS.Feed. Using the code below as a
starting point for a function should work. However, VSTS.Feed ignores the parameters. I've tried it with multiple projects and
I either get too many results (the initial problem), or for smaller projects, I get every field instead of just the ones selected.
let BaseURL = "https://server.analytics.visualstudio.com/DefaultCollection/project/_odata/WorkItemSnapshot", Select = "DateSK,WorkItemId,State,WorkItemType", Filter = "WorkItemType eq Bug and State ne Closed and State ne Removed and DateSK ge 20180517 and DateSK le 20180615", Source = VSTS.Feed(BaseURL, [Query=[select=#"Select",filter=#"Filter"]]) in SourceThe second problem is that none of these parameters to VSTS.Feed() are documented. There are no examples. This has even
been requested by ericleigh007 in this post over a year ago. The same is true for OData.Feed().
Overall, without working examples of VSTS.Feed and VSTS.AccountContents that can actually refresh, the basic problem
still remains.
Hi,
Is there an update on this? I am also having issues with Power BI dataset not being refreshed from VSTS Azure. I'm not getting any errors, but it's not refreshing either. I'm connecting to VSTS with OAuth2.
Thanks!
