Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
51 | |
19 | |
12 | |
11 |