Can't refresh OData feed because of dynamics url

[wewa]

I have run into trouble getting my data to refresh online outside of Power Bi Desktop. The offending query retrieves a table of all logins made during the past 2 months up to a max of 100000.

When I try to schedule refresh the following Data source:

Source = OData.Feed("https://xxxx.api.crm4.dynamics.com/XRMServices/2011/OrganizationData.svc/AuditSet?$select=UserId,ObjectId,Action,Operation,CreatedOn&$filter=(Operation/Value eq 1) and (CreatedOn ge datetime'" & DateTime.ToText(Date.AddMonths(DateTime.LocalNow(),-2), "yyyy-MM-dd") & "')&$top=100000")

I get the error:

"You can't schedule refresh for this dataset because one or more sources currently don't support refresh."

When I remove  and (CreatedOn ge datetime'" & DateTime.ToText(Date.AddMonths(DateTime.LocalNow(),-2), "yyyy-MM-dd") & "')

modifing the URL to:

Source = OData.Feed("https://xxxx.api.crm4.dynamics.com/XRMServices/2011/OrganizationData.svc/AuditSet?$select=UserId,ObjectId,Action,Operation,CreatedOn&$filter=(Operation/Value eq 1)&$top=100000")

The error dissapears.

It looks like the dynamic part of my URL doesn't work online. Will this be made possible in the future? Or is there a work arround?

[Vinothsusai]

Hello,

I am facing same issue. Do you have any solution for this?. Please advise.

Thanks

[Tishchenko]

https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power...

[wewa]

Thanks Tishchenko, that really helped clear up how this should work.

I am still running into an error though, so I can't accept your answer yet. I checked the documentation for OData.Feed and the same Query option is available. When I modify the query to:

Source = OData.Feed("https://xxxx.api.crm4.dynamics.com/XRMServices/2011/OrganizationData.svc/AuditSet?$select=UserId,ObjectId,Action,Operation,CreatedOn&$filter=1&$top=100000", [Query=[filter="(Operation/Value eq 1)"]])

I get the error:

Expression.Error: OData: The header with name 'Query' has a value type 'Record' that is invalid. Only DateTime, Logical, Number, and Text are supported.

I think Power Bi is parsing the Query option as a Header option. How do I skip the Header option?