Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am fetching data from Dynamics 365 with OData and I only want to fetch data that is 3 days old or newer.
I have this query and it works but the date is hardcoded as you can see.
= OData.Feed("https://sandbox.operations.dynamics.com/Data/SupplyForecastEntries?"
&"$select=LastDate, DeliveryDate, field3"
&"&$top=500"
&"&$filter=LastDate ge 2010-10-01" // Hard coded date
, null, [Implementation="2.0"])
Can I change the date part to be dynamic?
Solved! Go to Solution.
Something like:
Date.ToText(Date.AddDays(Date.From(DateTime.FixedLocalNow()), -3), "yyyy-MM-dd")
Check if this functions helps:
Hi,
I want to change date format in Odata query. It is taking value in format YYYY-MM-DD.
example of Odata query from Microsoft:
let
Source = OData.Feed ("https://analytics.dev.azure.com/{organization}/{project}/_odata/V3.0-preview/WorkItemBoardSnapshot?"
&"$apply=filter( "
&"Team/TeamName eq '{teamname}' "
&"and BoardName eq 'Stories' "
&"and DateValue ge 2023-10-18 "
&") "
&"/groupby( "
&"(DateValue,ColumnName,LaneName,State,WorkItemType,AssignedTo/UserName,Area/AreaPath), "
&"aggregate($count as Count) "
&") "
,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4])
in
Source
Now I want to change it to MM-DD-YYYY, can you please provide your input on this how we can convert this?
Something like:
Date.ToText(Date.AddDays(Date.From(DateTime.FixedLocalNow()), -3), "yyyy-MM-dd")
Check if this functions helps:
Thanks! Worked like a charm 🙂
@royalswe did this work for you in the service as well? I keep getting the error saying dynamic content can't be scheduled?
Thanks,
No It didn´t work when scheduled in services.
I had to make the date filter in another row in the M query.
Like this
let
Source = OData.Feed("https://sandbox.operations.dynamics.com/Data/SupplyForecastEntries?"
&"$select=StartDate, SupplyTypeId, LastDate"
// &"&$filter=ForecastStartDate ge " & Date.ToText(Date.AddDays(Date.From(DateTime.FixedLocalNow()), -3), "yyyy-MM-dd")
, null, [Implementation="2.0"])
, #"Filtered Rows" = Table.SelectRows(Source, each [StartDate] >= [LastDate])
in
#"Filtered Rows"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.