Forum Discussion

AI14's avatar
AI14
Helper III
3 years ago
Solved

Dynamically Change date range of web source (api link)

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

 

Need Help
Show and Tell
  • AntrikshSharma's avatar
    AntrikshSharma
    3 years ago

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

13 Replies

  • AntrikshSharma's avatar
    AntrikshSharma
    Community Champion
    3 years ago

    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's avatar
      AI14
      Helper III
      3 years ago

      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's avatar
        AntrikshSharma
        Community Champion
        3 years ago

        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's avatar
    AI14
    Helper III
    3 years ago

    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's avatar
      AntrikshSharma
      Community Champion
      3 years ago

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

      • AI14's avatar
        AI14
        Helper III
        3 years ago

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