Automatic refresh error when using VSTS.Feed with a parameter

Hi neuhofmo,

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

Best Regards,
Qiuyun Yu 

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 

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

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-in-power-query-and-power-bi-m-code/

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.

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!