Automatic refresh error when using VSTS.Feed with a parameter

[neuhofmo]

Hi, While easily refreshing and updating on power BI desktop, refresh doesn't work for a dataset/dashboard with a parameter in the VSTS.Feed URL. While this query *works*: let VSTSNonInfo = VSTS.Feed("https://myproject.analytics.visualstudio.com/WCDProd/_odata/WorkItems?$apply=filter(cast(ATDB_Report...) gt 2018-12-20T00:00:00Z)&$select=WorkItemId,Title") The following query *fails*, saying the data source does not support refreshing: let startDate = DateTimeZone.ToText(Date.AddDays(DateTimeZone.UtcNow(), -60), "yyyy-MM-ddThh:mm:ssZ"), VSTSNonInfo = VSTS.Feed("https://myproject.analytics.visualstudio.com/WCDProd/_odata/WorkItems?$apply=filter(cast(ATDB_Report...) gt " & startDate &")&$select=WorkItemId,Title") From playing around and from what I read elsewhere it seems like using parameters (such as "startDate" in the query works in the Power BI Desktop version but doesn't work for the online/scheduled refresh, which prevents me from keeping a constantly updated version of my dashboard. Please let me know if there's any solution for that. Thanks, Moran

[v-qiuyu-msft]

Hi @neuhofmo,

Please replace " & startDate &" as startDate then test again on your side. 

Best Regards,
Qiuyun Yu 

[neuhofmo]

Hi, Thanks for the response, but I am not sure I understand. Should I just write "(cast(ATDB_ReportTime,Edm.DateTimeOffset) gt startDate)" in the query? Because it doesn't work... Thanks! Moran

[v-qiuyu-msft]

Hi @neuhofmo,

Yes, I mean write "(cast(ATDB_ReportTime,Edm.DateTimeOffset) gt startDate)" in my previous post. The same query is refresh successfully on my side with Power BI service. 

Please go to Power BI service Refresh History, check detail error information for failure record, share these information with us. 

Best Regards,
Qiuyun Yu 

[neuhofmo]

Hi @v-qiuyu-msft 

What I meant is that when using your suggestion, the refresh does work, technically, but the queries are empty (as it's probably trying to compare a date to the string "startDate"). 

So technically I'm not seeing any error, but I am not seeing any data as well.

I am worried that there's might be something that I am missing in how to use parameters in VSTS.Feed online refresh.

Thanks,

Moran

[arsaveli]

You are correct in your research - powerbi.com prevents certain modifications of queries like these.

There are some workarounds for web.contents function here:

https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-i...

However i don't believe this trick works with odata as-is. You should use OData.Feed function (instead of VSTS.Feed) and then do the folding in desktop designer, i.e. start with complete /WorkItem  list like so:

OData.Feed("https://myproject.analytics.visualstudio.com/WCDProd/_odata/WorkItems?$select=WorkItemId,Title")

And then filter the column in query designer - this will add the parameter to your query programmatically and you may be able to modify it at runtime.

[neuhofmo]

Hi @arsaveli,

I switched to OData.Feed as you suggested and did the filtering after receiving the data.

It works, Thanks!

The problem I had was with retreiving too many rows from VSTS (apparently they have a certain limit) but I guess I'll have to overcome it in a different way.

Thanks!