The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
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
Solved! Go to Solution.
Thank you, the M code is working. However, like I said above, when this query resides within PowerBI dataflow, it does not work.
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
Thank you, the M code is working. However, like I said above, when this query resides within PowerBI dataflow, it does not work.
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
Thank you for sharing this! Needed this solution to avoid a Dynamic Data Source error in Power BI service.
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.
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
55 | |
42 | |
28 | |
26 | |
23 |