In Power Query in Excel, we do a LeftAnti join to filter a table. Then we remove the join column because we don't need it, we just needed the LeftAnti join to filter the table. This worked fine until recently, and now when we run the query, the anti join does not run and we get all the rows in the table. If we get rid of the Remove Columns step, it works properly, so I suspect Query Optimization is breaking this. I think Excel just updated to Version 2403 (Build 17425.20236 Click-to-Run) 64-bit, so I'll bet something changed in that version for Query Optimization. If we view the steps in Power Query, the Merged Queries step shows an empty table, then all the rows show after the Removed Columns step. I would consider this a bug because the whole point of a LeftAnti join is to filter a table. By defintion, the column we are removing only has null records, so there is never a reason to keep this column.
As a work around, we have switched to a left join, expanded the result, and then filter on nulls to do the same as a LeftAnti join. But again, this defeats the whole point of having a LeftAnti option if we have to do this.
let
Source = #"Accounts in Output",
#"Merged Queries" = Table.NestedJoin(Source, {"L_C", "A_N"}, #"Accounts", {"L", "A"}, "Accounts", JoinKind.LeftAnti),
#"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Accounts"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Last Refreshed", each LastRefreshedAt, type text)
in
#"Added Custom"
