topic Re: Dynamically Change date range of web source (api link) in Power Query

Dynamically Change date range of web source (api link)

AI14 — Sat, 19 Nov 2022 12:57:04 GMT

Im new to powerquery and have made a connection to a site using APi key.

im trying to avoid going into the source every day to look at yesterdays data or any previous day.

how can i do this dynamically where i choose e.g. 16/11/2022 and it populates both my from and to dates in my source

Json.Document(Web.Contents("w.findmyshift.co.uk/apixxxxxxxxx/1.4/reports/hours?" & "apiKey=&" & "teamId=y22t0l5t4i&" & "from=2022-11-16&" & "to=2022-11-16&" & "timesheetData=yes&" & "additional-columns=staff.jobtitle&" & "additional-columns=staff.department"))

Many Thanks

Re: Dynamically Change date range of web source (api link)

AntrikshSharma — Sat, 19 Nov 2022 13:14:50 GMT

AI14 You want a single day's data? You can replace those dates with this:

= Date.ToText ( Date.From ( DateTime.LocalNow ( ) ), [Format = "yyyy-MM-dd", Culture = "en-US"] )

Re: Dynamically Change date range of web source (api link)

AI14 — Sat, 19 Nov 2022 14:31:31 GMT

Thanks,

could this also be done if i set it yesterday instead of today?

also is it possible to have it linked to a cell where dat is entered?

Re: Dynamically Change date range of web source (api link)

AntrikshSharma — Sat, 19 Nov 2022 15:12:33 GMT

AI14 Are you using Excel for this? Then you can create a new query from a particular cell and then reference that query in the above code.

Re: Dynamically Change date range of web source (api link)

AI14 — Sat, 19 Nov 2022 15:15:23 GMT

yes i am, sorry i didnt mention it.

Re: Dynamically Change date range of web source (api link)

AI14 — Sat, 19 Nov 2022 15:16:35 GMT

would the code stay the same or do i need to reference the sheet, cell number?

Re: Dynamically Change date range of web source (api link)

AntrikshSharma — Sat, 19 Nov 2022 15:46:56 GMT

AI14 Create a table in Excel with name DateParameter

And then you can load the table into PQ, the code will look like this:

let Source = Excel.CurrentWorkbook(){[ Name = "DateParameter" ]}[Content], ChangedType = Table.TransformColumnTypes ( Source, { { "Date", type datetime } } ), Date = Date.ToText ( Date.From ( ChangedType[Date]{0} ), [ Format = "yyyy-MM-dd", Culture = "en-US" ] ) in Date

As you can see the the data type of quer is just scalar text value now you can use the name DateParameter in your original code.

Re: Dynamically Change date range of web source (api link)

AI14 — Sun, 20 Nov 2022 17:38:00 GMT

ammazing and would this work for both from & to dates?

Re: Dynamically Change date range of web source (api link)

AntrikshSharma — Sun, 20 Nov 2022 19:03:58 GMT

AI14 Did you try it?

Re: Dynamically Change date range of web source (api link)

AI14 — Sun, 20 Nov 2022 20:35:14 GMT

Date Parameter works but how do i incoporate it in code.

i tried to put it in like below and i tried how i usually had it "from=dateparameter&" etc.

none worked so far

= Json.Document(Web.Contents("https://www.findmyshift.co.uk/api/1.4/reports/hours?" & "apiKey=xxxxxx&" & "teamId=y22t0l5t4i&" & DateParameter & DateParameter & "timesheetData=yes&" & "additional-columns=staff.jobtitle&" & "additional-columns=staff.department"))

Re: Dynamically Change date range of web source (api link)

AntrikshSharma — Mon, 21 Nov 2022 07:04:41 GMT

AI14 You are missing "from" and "to" in your code! Check your question.

Re: Dynamically Change date range of web source (api link)

AI14 — Mon, 21 Nov 2022 09:37:33 GMT

I meant i done this also and still not pulling data.

usually date range is "form=2022-11-20&" "to=2022-11-20&"

= Json.Document(Web.Contents("https://www.findmyshift.co.uk/api/1.4/reports/hours?" & "apiKey=xxxxx&" & "teamId=y22t0l5t4i&" & from=DateParameter & to=DateParameter & "timesheetData=yes&" & "additional-columns=staff.jobtitle&" & "additional-columns=staff.department"))

Re: Dynamically Change date range of web source (api link)

AntrikshSharma — Mon, 21 Nov 2022 09:42:17 GMT

AI14You need to include to and from in double quotes .... & "from=" & DateParameter & "to=" & DateParameter & ....

Re: Dynamically Change date range of web source (api link)

AI14 — Mon, 21 Nov 2022 14:33:36 GMT

= Json.Document(Web.Contents("https://www.findmyshift.co.uk/api/1.4/reports/hours?" & "apiKey=xxxxx&" & "teamId=y22t0l5t4i&" & "from=" & DateParameter & "to=" & DateParameter & "timesheetData=yes&" & "additional-columns=staff.jobtitle&" & "additional-columns=staff.department"))

not working, i think its because its creating additional line in code when the date should be after the from= and to=