I have made the following query, but i want to change the dates to variables, that change with respect to the current time. So if i call the API at some point, i want the data from the current time and for example 24 hours back. Is this possible?

Thanks in advance, guys! :)

let
   Source = Json.Document(Web.Contents("SOMEURL&fromDate=2024-11-18+16%3A42%3A01&toDate=2024-11-18+18%3A42%3A01&fields=",
       [Timeout=#duration(0, 0, 12, 0), 
       Headers=[Authorization="Bearer API TOKEN", 
       Accept="application/json"]])), 

   JsonResponse = Json.Document(Source),

   Data = JsonResponse, 
   Table = Table.FromRecords(Data)
in
   Table

I have made the following query, but i want to change the dates to variables, that change with respect to the current time. So if i call the API at some point, i want the data from the current time and for example 24 hours back. Is this possible?

Thanks in advance, guys! :)

let
   Source = Json.Document(Web.Contents("SOMEURL&fromDate=2024-11-18+16%3A42%3A01&toDate=2024-11-18+18%3A42%3A01&fields=",
       [Timeout=#duration(0, 0, 12, 0), 
       Headers=[Authorization="Bearer API TOKEN", 
       Accept="application/json"]])), 

   JsonResponse = Json.Document(Source),

   Data = JsonResponse, 
   Table = Table.FromRecords(Data)
in
   Table

• powerbi
• powerquery
• powerbi-desktop

1 Answer 1

Perhaps:

let

   To = DateTime.ToText(DateTime.FixedLocalNow(),"yyyy-MM-dd+hh\%3Amm\%3Ass"),
   From = Text.ReplaceRange(To,8,2,Number.ToText(Number.From(Text.Range(To,8,2)) - 1,"00")),

   URL = "SOMEURL&fromDate=" & From & "&toDate=" & To & "fields=",
   Source = Json.Document(Web.Contents(URL,
       [Timeout=#duration(0, 0, 12, 0), 
       Headers=[Authorization="Bearer API TOKEN", 
       Accept="application/json"]])), 

   JsonResponse = Json.Document(Source),

   Data = JsonResponse, 
   Table = Table.FromRecords(Data)
in
   Table

Note that we use text-based math to convert the 2nd time so as not to have to deal with any time difference between the different calls to the DateTime.FixedLocalNow() function. Although the difference might be in fractions of a second, if that difference fell on the transition from one second to the next, it could result in different times being returned.