How can a custom column in one PowerQuery reference another PowerQuery

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.

Do both tables have the same number of rows? If you add an index column you can reference the row of QueryOne and then reference the column. For example, if your index column is called Index, then QueryOne{Index}[DataValue] will access the value in the DataValue column at that row.

In general, you can use the query's name to access its value in the custom column formula. QueryOne will give you the table from QueryOne. If your query has spaces or other special characters in its name, like in Query One, then #"Query One" will get its value.