powerbi - Can i insert variables into a Power Query? - Stack Overflow

admin管理员组

文章数量:1334887

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

Share Improve this question Follow asked Nov 20, 2024 at 19:06 owl_inowl_in 3133 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

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.

本文标签： powerbiCan i insert variables into a Power QueryStack Overflow