I want to use data from one power query in the creation of a custom column in a second power query, but not sure how to do it (or if I'm going about this the wrong way).

I have one query (QueryOne) that ends up returning an integer column (DataValue) and a datetime column (DateOfCompletion). I then have a second query (QueryTwo) that have a PeriodName, StartDate, and EndDate. What I'm after, is on the second query to have a calculated column that is the sum of QueryOne.DataValue where QueryOne.DateOfCompletion is between QueryTwo.StartDate and QueryTwo.EndDate. (so I get the DataValue of items completed by the PeriodName)

I cannot seem to make reference to QueryOne when creating the custom column on QueryTwo; is this possible or is there a standard method of combining data of that form?

Here's one way...

If I start with this as QueryOne:

And this as QueryTwo:

I can get this result with the code below:

Here's the M code:

let
Source = QueryTwoSource,
#"Added Custom" = Table.AddColumn(Source, "QueryOne", each QueryOne),
#"Expanded QueryOne" = Table.ExpandTableColumn(#"Added Custom", "QueryOne", {"DateValue", "DateOfCompletion"}, {"QueryOne.DateValue", "QueryOne.DateOfCompletion"}),
#"Added Custom1" = Table.AddColumn(#"Expanded QueryOne", "Custom", each if ([QueryOne.DateOfCompletion] >= [StartDate]) and ([QueryOne.DateOfCompletion] <= [EndDate]) then "True" else "False"),
#"Renamed Columns" = Table.RenameColumns(#"Added Custom1",{{"Custom", "WithinPeriod"}}),
#"Grouped Rows" = Table.Group(#"Renamed Columns", {"PeriodName", "WithinPeriod"}, {{"CountWithinPeriod", each Table.RowCount(_), type number}, {"SumOfDateValuesWithinPeriod", each List.Sum([QueryOne.DateValue]), type number}, {"AllData", each _, type table}}),
#"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([WithinPeriod] = "True")),
#"Expanded AllData" = Table.ExpandTableColumn(#"Filtered Rows", "AllData", {"QueryOne.DateValue", "QueryOne.DateOfCompletion"}, {"QueryOne.DateValue", "QueryOne.DateOfCompletion"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded AllData",{"WithinPeriod"})
in
#"Removed Columns"

p.s. The Source in the code above is just another query of a spreadsheet table that has the three columns: PeriodName, StartDate, and EndDate. ...Similar to how QueryOne is a query of a spreadsheet table with DateValue and DateOfCompletion columns. So, basically, Source = QueryTwoSource is how I got QueryTwo, pictured above. I could have directly used the spreadsheet table as QueryTwo's source instead, but this is how I did it.