Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to Solution.
@AI14You need to include to and from in double quotes .... & "from=" & DateParameter & "to=" & DateParameter & ....
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"))
not working, i think its because its creating additional line in code when the date should be after the from= and to=
@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
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.
ammazing and would this work for both from & to dates?
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"))
Check out the July 2025 Power BI update to learn about new features.