topic Re: OData URL filter based on current Date. in Power Query

OData URL filter based on current Date.

JoseXaviHiguera — Fri, 27 Oct 2023 19:29:32 GMT

In the advance editor I had this query:

let
FechaInicioStr = "2023-07-27",
Source = OData.Feed("https://xxxx.crm.dynamics.com/api/data/v9.0/audits?$filter=createdon gt "
& FechaInicioStr
& " and action eq 64&$select=_objectid_value,createdon", null, [Implementation="2.0"])
in
Source

This runs ok, but i want to make the date change automatically everytime I refresh the data online.

FechaInicioStr =Date.ToText(Date.AddMonths(Date.From(DateTime.LocalNow()),-3), "yyyy-MM-dd"),

I made this change, and it runs ok on desktop, but when I programmed it online I get the following error:

This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed. Learn more: https://aka.ms/dynamic-data-sources.

Does anyone knows how to automate the date to the current date when we update the data online?

Re: OData URL filter based on current Date.

ppm1 — Sat, 28 Oct 2023 12:12:50 GMT

Please see this article for how to avoid that error - OData.Feed And The Dynamic Data Sources Error In Power BI (crossjoin.co.uk).

Also, you could switch to Web.Contents and use the RelativePath option (links about that in that article too).

Pat

Re: OData URL filter based on current Date.

JoseXaviHiguera — Sat, 28 Oct 2023 18:12:09 GMT

The problem was not on the filter, but in looking for a DateTime.LocalNow(), do you happen to have another way of searching for a current date?

Re: OData URL filter based on current Date.

JoseXaviHiguera — Mon, 30 Oct 2023 17:49:01 GMT

The solution in the link was with a merge, but with a Query inside OData.Feed works correctly, here's is the solution