Forum Discussion
List.Max on Table.Select with Field Filter
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 |
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
2 Replies
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