Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
But my Campaigns have start dates like:
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 |
Solved! Go to 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
@RamRock
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
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