Forum Discussion
Dynamic Date function as parameter in JSON data source not working
Community ChampionYou sem to be getting two different types of errors, one related to the code but another one realted to the web site not being reachable. Are you sure the URL you put together actually is actually reachable? Try this:
let
GetTodayDate = Text.From(DateTime.LocalNow()),
SevenDaysAgo = Text.From(Date.AddDays(DateTime.LocalNow(),-7)),
Source = Web.Contents("http://our secret URL/select?defType=lucene&facet=false&q=orderDate:%5B"& SevenDaysAgo &"+TO+"& GetTodayDate &"%5D&rows=5000000&sort=orderDate+desc&start=0" & "&wt=json")
in
SourcePlease mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs
Cheers
Thanks we are 1/2 way there now!
I changed yours to add the Zone, since it requires a DateTimeZone format but it is different zone format
AND no .format is available.
so i need a way now to format the zone string in this format.
{date.format("yyyy-MM-dd'T'HH:mm:ssZ").tostring()}
GetTodayDate = Text.From(DateTimeZone.LocalNow()),
SevenDaysAgo = Text.From(Date.AddDays(DateTimeZone.LocalNow(),-7)),
DataSource.Error: Web.Contents failed to get contents from 'http://secret URL/select?defType=lucene&facet=false&q=orderDate:%5B5/12/2020%202:25:36%20PM%20-05:00+TO+5/19/2020%202:25:36%20PM%20-05:00%5D&rows=50000&sort=orderDate+desc&start=0&wt=json' (400): Bad Request
that seems to work for the first part because it does put a date in, however the format is wrong.
5/12/2020%202:25:36%20PM%20-05:00
2020-05-07T00:00:00.000Z
It has to use dashes and THIS DateTimeZone format. do you know of a .format command that works with DateTimeZone?
- AlB
Is the time that yopu want always 00:00:00.000? If so, use this:
GetTodayDate = Text.Replace(Text.Start(Text.From(DateTime.LocalNow()),10),"/","-") & "T00:00:00.000Z", SevenDaysAgo = Text.Replace(Text.Start(Text.From(Date.AddDays(DateTime.LocalNow(),-7)),10),"/","-") & "T00:00:00.000Z"Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs
Cheers
Getting really close. I think it is flipping the format of the date around now.
5-12-2020 T00:00:00.000Z TO 5-19-2020 T00:00:00.000Z
needs to be:
2020-05-12T00:00:00.000Z TO 2020-05-19T00:00:00.000Zis there no date format, since you are doing text replaces?