List.Max on Table.Select with Field Filter

[Anonymous]

I've got a Calendar query and a "Campaign" query and I'm trying to bring in the Campaign Start Date into my Calendar query. I'm trying to add a custom column:

Table.AddColumn(#"Changed Date column to Date Type","Campaign Start",each List.Max(Table.SelectRows(#"Campaign Dates", each [Starting_Date] <= [Date])[Starting_Date]))

Where [Date] is the date in my Calendar query, but I get the error of:

Expression.Error: The field 'Date' of the record wasn't found.

For example, my Calendar might have:

• 01/01/2021
• 01/02/2021
• 01/03/2021
• 01/04/2021
• 01/05/2021

But my Campaigns have start dates like:

• 01/01/2021
• 01/05/2021

I'm essentially trying to mimic the Excel VLOOKUP(false) function in my Calendar query, where I retrieve results like:

Date	Campaign Start
01/01/2021	01/01/2021
01/02/2021	01/01/2021
01/03/2021	01/01/2021
01/04/2021	01/01/2021
01/05/2021	01/05/2021

[Anonymous]

@Anonymous 
You cannot refer to the column in other tables in power query. You need do some transformation(merge,append,etc) to combine the date column into the Campaign query.

Paul Zheng _ Community Support Team

[Anonymous · Accepted solution]

I would use the Merge GUI function (Join) to Join your calendar table to your campaign start table, and then fill down the nulls, like:

Table.Join(Calendar, {"Date"}, #"Campaign Start", {"Starting_Date"}, JoinKind.LeftOuter)

Now you can just use the FillDown button in the GUI to replace the nulls in the Campaign Start column.

--Nate