Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
AI14
Helper III
Helper III

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

 

1 ACCEPTED SOLUTION

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

View solution in original post

13 REPLIES 13
AI14
Helper III
Helper III

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

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

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

AI14_0-1669041095781.png

 

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

AI14_1-1669041203826.png

 

AntrikshSharma
Super User
Super User

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

 

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?

 

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

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

yes i am, sorry i didnt mention it.

 

@AI14 Create a table in Excel with name DateParameter

AntrikshSharma_1-1668872784302.png

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

AntrikshSharma_0-1668872739834.png

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.

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

@AI14 Did you try it?

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

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

Top Solution Authors