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.
I have a query called, "Calculations" with a single record with a field ("ReportString") that holds a string that needs to be used in another query. For example, today that string is "2023-04-20T06:00:00Z". This cannot be obtained directly with a utcnow function because it must be 6:00:00Z, regardless of when it is called, so I piece this value together to create "ReportString". So far, so good.
I have an analytics query that uses a source from "Windows Update for Business" data. The query statement reads (in part):
[Query=[#"query"="let _SnapshotTime = datetime(2023-04-20T06:00:00Z);
UCClient
| where TimeGenerated == _SnapshotTime
| summarize SecurityUpdateStatus = count() by OSSecurityUpdateStatus
",#"x-ms-app"=
I want to replace the value in the datetime function (which is currently hard-coded) with the value from the "Calculations" query (where the value is called, "ReportString").
Normally, I could use "List.Min(Calculations[ReportString])" to get the value. (Since there is only one record, List.Min gives me the first and only record). This works great if I put it in a step after the source step... but I can't get it to work inside the source step.
I've tried things like:
[Query=[#"query"="let _SnapshotTime = datetime(List.Min(Calculations[ReportString]));
UCClient
| where TimeGenerated == _SnapshotTime
| summarize SecurityUpdateStatus = count() by OSSecurityUpdateStatus
",#"x-ms-app"=
...but it seems to get all hung up because it doesn't want to expand that since it's inside the quotes that start the query statement.
Any ideas how to attack this?
Solved! Go to Solution.
After trying multiple things, I finally ran into one that worked.
Essentially, If I declare a variable at the top:
let RDS = List.Min(Calculations[ReportString]),
AnalyticsQuery =
I can then do:
[Query=[#"query"= "let _SnapshotTime = datetime("&RDS&");
UCClient
After trying multiple things, I finally ran into one that worked.
Essentially, If I declare a variable at the top:
let RDS = List.Min(Calculations[ReportString]),
AnalyticsQuery =
I can then do:
[Query=[#"query"= "let _SnapshotTime = datetime("&RDS&");
UCClient
Check out the July 2025 Power BI update to learn about new features.