Variables in Analytics Query Source

[korebreach]

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?

[korebreach · Accepted 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