Dynamically Change date range of web source (api link)

[AI14]

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

[AI14]

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"))

[AntrikshSharma · Accepted solution]

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

[AI14]

= 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=

[AntrikshSharma]

@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"] 
)

[AI14]

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?

[AntrikshSharma]

@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.

[AI14]

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

[AI14]

yes i am, sorry i didnt mention it.

[AntrikshSharma]

@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.

[AI14]

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

[AntrikshSharma]

@AI14 Did you try it?

[AI14]

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"))

[AntrikshSharma]

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