topic Re: Add dynamic dates in API Source url in Developer

Add dynamic dates in API Source url

DryMouse555 — Thu, 27 Jun 2024 23:37:25 GMT

Hi,

I have connected to a online system using API url and Auth key. Below is my code. This I enter in the blank query as source step.

= Json.Document(Web.Contents("https://xyz.com.au/api/",
[Query=[
dbtable="statementitems",
fundingtype= "2",
DateStart = "Date.IsInPreviousMonth",
DateEnd = "Date.IsInCurrentDay",
Authkey = ApiKey,
includedeleted = "yes",
toprestrict = "1000000",
source= "powerBI"
]]
))

I want to add in DateStart where whenever it refreshed, it pulls the last 3 months data and DateEnd to Current day.
Can you tell me how to do that? Thanks in advance.

Re: Add dynamic dates in API Source url

Anonymous — Fri, 28 Jun 2024 02:46:13 GMT

Hi @DryMouse555 ,

To dynamically set the 'DateStart' to pull data from the last 3 months and 'DateEnd' to the current day in your API call, please modify the codes as below and check if it can work:

Date.AddMonths - PowerQuery M | Microsoft Learn

let EndDate= DateTime.LocalNow(), StartDate = Date.AddMonths(DateTime.Date(EndDate), -3), DateStart = DateTime.ToText(StartDate, "yyyy-MM-dd"), DateEnd = DateTime.ToText(Today, "yyyy-MM-dd"), apiUrl = "https://xyz.com.au/api/", Source = Json.Document(Web.Contents(apiUrl, [ Query = [ dbtable = "statementitems", fundingtype = "2", DateStart = DateStart, DateEnd = DateEnd, Authkey = ApiKey, includedeleted = "yes", toprestrict = "1000000", source = "powerBI" ] ] )) in Source

Best Regards

Re: Add dynamic dates in API Source url

DryMouse555 — Mon, 01 Jul 2024 02:35:08 GMT

Hi,

Sorry for the late reply.

My input:

let
EndDate= DateTime.LocalNow(),
StartDate = Date.AddMonths(DateTime.Date(EndDate), -3),
DateStart = DateTime.ToText(StartDate, "dd/mm/yyyy"),
DateEnd = DateTime.ToText(Today, "dd/mm/yyyy"),
apiUrl = "https://xyz.com.au/api/",
Source = Json.Document(Web.Contents(apiUrl,
[
Query = [
dbtable = "statementitems",
fundingtype = "2",
DateStart = DateStart,
DateEnd = DateEnd,
Authkey = ApiKey,
includedeleted = "yes",
toprestrict = "1000000",
source = "powerBI"
]
]
))
in
Source

I am getting this message

Re: Add dynamic dates in API Source url

Anonymous — Mon, 01 Jul 2024 06:25:13 GMT

Hi @DryMouse555 ,

Base on the error message in the screenshot, it seems that there’s an issue with the date format or conversion. Please update the codes as below and check if it can return the expected data...

let EndDate= DateTime.LocalNow(), StartDate = Date.AddMonths(DateTime.Date(EndDate), -3), DateStart = Date.ToText(StartDate, "yyyy-MM-dd"), DateEnd = Date.ToText(DateTime.Date(EndDate), "yyyy-MM-dd"), apiUrl = "https://xyz.com.au/api/", Source = Json.Document(Web.Contents(apiUrl, [ Query = [ dbtable = "statementitems", fundingtype = "2", DateStart = DateStart, DateEnd = DateEnd, Authkey = ApiKey, includedeleted = "yes", toprestrict = "1000000", source = "powerBI" ] ] )) in Source

Best Regards

Re: Add dynamic dates in API Source url

DryMouse555 — Mon, 01 Jul 2024 23:45:04 GMT

Hi, I am still getting these errors. I have added the code above.

Also, before I put static date values, I used this code. I was told to use this date format to get the output

= Json.Document(Web.Contents("https://xyz.com.au/api/",
[Query=[
dbtable="statementitems",
fundingtype= "2",
DateStart = "01/Jun/2024",
Date.End = "29/Jun/2024",
Authkey = ApiKey,
includedeleted = "yes",
toprestrict = "1000000",
source= "powerBI"
]]
))

Re: Add dynamic dates in API Source url

DryMouse555 — Tue, 02 Jul 2024 05:44:16 GMT

Hi, I am still getting these errors. I have added the code above.

Also, before I put static date values, I used this code. I was told to use this date format to get the output