OData query to limit records to last days ("sub duration" seems not to work)

[RafalMonka]

Hello Everyone,

I am working on Azure DevOps analytics with OData query which resides in Power BI dataflow.

What I need is to limit records to last 3 days. Can anyone help me?

I have this query, but it does not work:

let
  Source = OData.Feed(
    "https://analytics.dev.azure.com/" & paramOrganization & "/" & paramProjectName & "/_odata/v4.0-preview/WorkItemRevisions?
    $apply=compute(
      now() sub duration'P3D' as paramDateFrom
    )
    &$filter=StateChangeDate ge paramDateFrom 
    &$select=WorkItemId, State, StateChangeDate, paramDateFrom", 
    null, 
    [Implementation = "2.0", ODataVersion = 4]
  )
in
  Source

DataSource.Error: OData: Request failed: The remote server returned an error: (400) Bad Request. (VS403534: Arithmetic expressions over arguments without a common numeric type is not supported.)

Query that works but without condition for last days

let
  Source = OData.Feed(
    "https://analytics.dev.azure.com/" & paramOrganization & "/" & paramProjectName & "/_odata/v4.0-preview/WorkItemRevisions?
    $apply=compute(
      now() as paramDateFrom
    )
    &$filter=StateChangeDate ge paramDateFrom 
    &$select=WorkItemId, State, StateChangeDate, paramDateFrom", 
    null, 
    [Implementation = "2.0", ODataVersion = 4]
  )
in
  Source

When I moved condition away from OData URL query, it works, but ... you can not save the dataflow then.

Can't save dataflow

One or more tables references a dynamic data source.

let
  paramDateFrom = Date.ToText(Date.AddDays(Date.From(DateTimeZone.LocalNow()),-3), "yyyy-MM-dd"),
  Source = OData.Feed(
    "https://analytics.dev.azure.com/" & paramOrganization & "/" & paramProjectName & "/_odata/v4.0-preview/WorkItemRevisions?
    $filter=StateChangeDate ge " & paramDateFrom & " 
    &$select=WorkItemId, State, StateChangeDate", 
    null, 
    [Implementation = "2.0", ODataVersion = 4]
  )
in
  Source

The workaround to query all records in first step in Power Query and, in second step, to add condition to filter records is not viable due to huge number (3M+) of all records returning by the first step. Such solution I have found in Dynamic date in Odata query 

[RafalMonka · Accepted solution]

Thank you, the M code is working. However, like I said above, when this query resides within PowerBI dataflow, it does not work.

Can't save dataflow. One or more tables references a dynamic data source.

But someone from Micrososft gave a solution

let
  Source = OData.Feed(
    "https://analytics.dev.azure.com/" & paramOrganization & "/" & paramProjectName & "/_odata/v3.0-preview/WorkItemRevisions?"
    & "$skip=0"
    & "&$filter=StateChangeDate ge @paramDateFrom "
    & "&$select=WorkItemId, State, StateChangeDate",
    null, 
    [
      Implementation = "2.0",
      Query= [#"@paramDateFrom" = Date.ToText(Date.AddDays(Date.From(DateTimeZone.LocalNow()), -3), "yyyy-MM-dd")]
    ]
  )
in
  Source 

[Anonymous]

Thank you for sharing this! Needed this solution to avoid a Dynamic Data Source error in Power BI service.

[lbendlin]

You can make a compromise.  Keep the URL static and use the Query parameter to handle the filters.

OData.Feed - PowerQuery M | Microsoft Learn

Not as good as RelativePath but better than what you have.

[StephenRabobank]

I know this was a while ago, but I read your answer but do not know how he would have implemented it. How would the OData.Feed have been constructed using his example?

[Anonymous]

Hi @RafalMonka ,

Please try below M code:

let
    ThreeDaysAgo = Text.From(DateTime.LocalNow() - #duration(3,0,0,0)),
    FilteredUrl = "https://analytics.dev.azure.com/" & paramOrganization & "/" & paramProjectName & "/_odata/v4.0-preview/WorkItemRevisions?$filter=StateChangeDate ge " & ThreeDaysAgo & "&$select=WorkItemId, State, StateChangeDate",
    Source = OData.Feed(FilteredUrl, null, [Implementation="2.0", ODataVersion=4])
in
    Source

Make sure that `ThreeDaysAgo` is formatted correctly to match the `StateChangeDate` in your dataset.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.