topic Re: Dynamic Date function as parameter in JSON data source not working in Power Query

Dynamic Date function as parameter in JSON data source not working

landcruiser64 — Tue, 19 May 2020 16:55:34 GMT

--------------------------------------------------------------------------------------------------------

Goal: Pass Date Parameters to the Data Source pointing to JSON to get data from 7 days ago until today

-------------------------------------------------------------------------------------------

JSON Data Source Connection String

http://our secret URL/select?defType=lucene&facet=false&q=orderDate:%5B2020-05-07T00:00:00.000Z+TO+2020-05-14T23:59:59.999Z%5D&rows=5000000&sort=orderDate+desc&start=0

%5B

2020-05-07T00:00:00.000Z - needs to be replaced with a dynamic date of 7 days ago

+TO+

2020-05-14T23:59:59.999Z - needs to be replaced with a dynamic date of Today

%5D

We also tried lots of different formatting things and syntax like:

{date.addHours(-15).format("yyyy-MM-dd'T'HH:mm:ssZ").tostring()}

{date.today.format().tostring()}

But nothing seems to work.

-------------------------------------------------------------------------------------------

4 solutions

----------------------------------------------------

1 Date Functions in the Data Source Direct

2 Parameter

3 Function

4 Query

----------------------------------------------------

1 Date Functions in the Data Source Direct

= Json.Document(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"))

Expression.Error: We cannot apply operator & to types Text and Function.

----------------------------------------------------

2 Parameter

Parameters setup: SevenDaysAgo and GetTodayDate

-- WORKS when I hard code these to datetimezone values if works fine but NOT dynamic

SevenDaysAgo = 2020-05-07T00:00:00.000Z

GetTodayDate = 2020-05-14T23:59:59.999Z

SevenDaysAgo = 2020-05-07

GetTodayDate = 2020-05-14 - which makes it a TimeZONE issue, what TimeZONE does Dax Return for DateTimeZone?

#datetimezone(2011, 8, 16, 23, 34, 37.745, 0, 0)

"12/31/2010 11:56:02 AM +08:00"

DataSource.Error: Web.Contents failed to get contents from

DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ssZ");

ZonedDateTime zdt = ZonedDateTime.parse("2017-01-11T09:38:41+0100", dtf);

-- if I add a DateTImeZone.UtcNow or LocalNow as the parameter value

Expression.Error: We cannot apply operator & to types Text and Function.

----------------------------------------------------

3 Function

GetNow = () => "DateTimeZone.UtcNow"

Expression.Error: We cannot apply operator & to types Text and Function.

----------------------------------------------------

4 Query

GetToday = DateTimeZone.UtcNow

DataSource.Error: Web.Contents failed to get contents from

----------------------------------------------------

Re: Dynamic Date function as parameter in JSON data source not working

AlB — Tue, 19 May 2020 19:10:28 GMT

Hi landcruiser64

You 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 Source

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

Re: Dynamic Date function as parameter in JSON data source not working

landcruiser64 — Tue, 19 May 2020 19:29:38 GMT

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?

Re: Dynamic Date function as parameter in JSON data source not working

AlB — Tue, 19 May 2020 20:06:29 GMT

landcruiser64

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

Re: Dynamic Date function as parameter in JSON data source not working

landcruiser64 — Tue, 19 May 2020 20:25:10 GMT

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.000Z

is there no date format, since you are doing text replaces?