filter by date on Odata query

[venBI]

I want to use some thing like this to get data for past X weeks or days. how can I do that without getting dynamic data sources error after I upload to power BI Service. is there any other way to limit data ? we are reporting for a azure devops project

let
// Calculate the start date (6 weeks ago)
StartDate = Date.AddWeeks(DateTime.LocalNow(), -6),
IterationStartDate = DateTime.ToText(StartDate, "yyyy-MM-ddTHH:mm:ssZ"),
IterationEndDate = DateTime.ToText(DateTime.LocalNow(), "yyyy-MM-ddTHH:mm:ssZ"),
ODataUrl = "https:abc/_odata/v4.0-preview/WorkItemSnapshot?
$filter=(WorkItemType eq 'Defect' or WorkItemType eq 'Task'
or WorkItemType eq 'User Story' or WorkItemType eq 'Bug') and
DateValue ge " & IterationStartDate & " and DateValue le " & IterationEndDate & " &
$select=WorkItemId,StoryPoints,WorkItemType,Title,State,Custom_Status,DateValue,ClosedDate&
$expand=Iteration,Area&",
Source = OData.Feed(ODataUrl, null, [Implementation="2.0", OmitValues = ODataOmitValues.Nulls, ODataVersion = 4]),
#"Expanded Iteration" = Table.ExpandRecordColumn(Source, "Iteration", {"StartDate", "EndDate", "IterationPath"}, {"IterationStartDate", "IterationEndDate", "IterationPath"}),
#"Expanded Area" = Table.ExpandRecordColumn(#"Expanded Iteration", "Area", {"AreaPath"}, {"AreaPath"})
in
#"Expanded Area"

[v-veshwara-msft]

Hi @venBI ,
Thanks for posting in Microsoft Fabric Community,
Apologies for the delayed response. Have you found a solution to your query? If not, please consider the following response.

The issue you're encountering is caused by using a dynamically constructed OData URL with date parameters. Power BI considers this a dynamic data source, which prevents scheduled refresh in Power BI Service.

To resolve this, you should:

1. Use a static OData URL to fetch the full dataset.
2. Apply the date range filter inside Power Query instead of passing it as a parameter in the API request.

Here’s an updated version of your query that avoids the dynamic data source error:

let
// Define Start Date (6 weeks ago)
StartDate = DateTime.From(Date.AddWeeks(DateTime.LocalNow(), -6)),
EndDate = DateTime.LocalNow(),
// Static OData URL (No dynamic parameters)
ODataUrl = "https://abc/_odata/v4.0-preview/WorkItemSnapshot",
Source = OData.Feed(ODataUrl, null, [Implementation="2.0", OmitValues = ODataOmitValues.Nulls, ODataVersion = 4]),

FilteredData = Table.SelectRows(Source, each [DateValue] >= StartDate and [DateValue] <= EndDate),
#"Expanded Iteration" = Table.ExpandRecordColumn(FilteredData, "Iteration", {"StartDate", "EndDate", "IterationPath"}, {"IterationStartDate", "IterationEndDate", "IterationPath"}),
#"Expanded Area" = Table.ExpandRecordColumn(#"Expanded Iteration", "Area", {"AreaPath"}, {"AreaPath"})
in
#"Expanded Area"

This approach ensures that Power BI can process the filtering without violating scheduled refresh rules. Let us know if you face any errors.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and a kudos would be appreciated.

Regards,
Vinay.

[venBI]

Sorry for the delayed response! First off, I should mention that I’m a newbie in Power Query and Power BI. Regarding your solution, does it pull all records from the server? While filtering after pulling the data helps, doesn’t that rely on local resources for filtering? Ideally, I’d prefer to use server-side resources for this filtering.

[v-veshwara-msft]

Hi @venBI ,

Thanks for your response.
As you mentioned, filtering data after retrieval relies on local resources, whereas applying filters at the server level is more efficient.

To ensure that filtering happens on the server side while avoiding the dynamic data source issue, the best approach as suggested by @lbendlin is to use Query parameters (Query=[...]) in OData.Feed instead of embedding filters directly in the URL. This allows Power BI to send filter conditions as parameters while keeping the base URL static, ensuring compatibility with scheduled refresh.

If your OData source supports query parameters, you can structure your request using the Query option with parameters like @StartDate and @EndDate, ensuring that Power BI handles filtering efficiently without violating refresh rules.

Let us know if you need further clarification!

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and a kudos would be appreciated.

Regards,
Vinay.

[lbendlin]

Please read the documentation.  Use the Query parameter

OData.Feed - PowerQuery M | Microsoft Learn

[venBI]

Thanks for you response, I have tried some this like this but still ends up with same error.

let
// Construct the full URL with query parameters
ODataUrl = "mylink/_odata/v4.0-preview/WorkItemSnapshot?" &
"$filter=" & Uri.EscapeDataString("(WorkItemType eq 'Defect' or WorkItemType eq 'Task' or WorkItemType eq 'User Story' or WorkItemType eq 'Bug') and " &
//"DateValue ge " & IterationStartDate & " and DateValue le " & IterationEndDate & " and " &
"DateValue ge @IterationStartDate and DateValue le @IterationEndDate )" &
"&$select=WorkItemId,StoryPoints,WorkItemType,Title,State,Custom_Status,DateValue,ClosedDate" &
"&$expand=Iteration,Area",

// Source with query parameters
Source = OData.Feed(
ODataUrl,
null,
[
Implementation = "2.0",
Query = [
// Calculate the start date (6 weeks ago)
#"@IterationStartDate" = DateTime.ToText(Date.AddWeeks(DateTime.LocalNow(), -6), "yyyy-MM-ddTHH:mm:ssZ"),
#"@IterationEndDate" = DateTime.ToText(DateTime.LocalNow(), "yyyy-MM-ddTHH:mm:ssZ")
]
]
)
in
Source

[lbendlin]

Please read the documentation.  Use the Query parameter. You are still trying to stuff the filters in the base URL.