OData Dynamic Date Filter - (The right side of the "GreaterThan" operator can't be NULL.)

[A_Yao]

I am trying to query Dynamics from my Power BI dashboard using a dynamic date filter through ODATA, as I only need the last 8 weeks and the dataset takes up almost 200x my original dashboard size if I were to download it all before filtering it. I have managed to get it to work properly inside of Power BI desktop, but after I upload it to the service for sharing, when setting up the credentials for automatic refreshes, it returns the following error: The right side of the "GreaterThan" operator can't be NULL.

Here is the query (with some names censored):

let
    last8weeks = Date.ToText(Date.AddWeeks(Date.From(DateTime.LocalNow()),-8), "yyyy-MM-dd"),
    Source = OData.Feed(
        "https://website.api.crm.dynamics.com/api/data/v9.2/dataset?$filter=date gt @filter&$select=datapoint1, datapoint2",
        null, 
        [
            Implementation="2.0",
            Query =
                [
                    #"@filter" = last8weeks
                ]
        ]
    )
in
    Source

I'm assuming Power BI Service is running into that @filter call in the filter section of the URL, but it works perfectly fine in Power Query on the desktop. Why is that? And is there any way to resolve this so that I can enable scheduled refresh again?

[v-saisrao-msft]

Hi @A_Yao,

Checking in to see if your issue has been resolved. let us know if you still need any assistance.

Thank you.

[v-saisrao-msft]

Hi @A_Yao,

Have you had a chance to review the solution we shared earlier? If the issue persists, feel free to reply so we can help further.

Thank you.

[v-saisrao-msft · Accepted solution]

Hi @A_Yao,

Thank you @Hans-Georg_Puls, for your insights.

Power BI Service ignores OData query options in M, so your dynamic filter (@filter) becomes NULL, causing the “GreaterThan operator can’t be NULL” error this is an official limitation of OData.Feed in the Service.

Power Query OData Feed connector - Power Query | Microsoft Learn

OData.Feed And The Dynamic Data Sources Error In Power BI

Thank you.

[Hans-Georg_Puls]

Hi @A_Yao ,

have you tried to move down the whole "$filter=date gt @filter&$select=datapoint1, datapoint2" part down to the Query section of your header?

I'm not completely sure what your "#"@filter" = last8weeks" entry does. Usually such an entry replaces the corresponding query entry of your url. That too could cause problems. 

Hope that helps.