PowerQuery - Filter rows that do not exist in another Table.[Field] list of values

Question

I have a PowerQuery M script that is supposed to filter the table and keep rows that do not exist in another Table.[Field] list.

The source table has 2489 rows. The lookup list has 1828 rows. But my script runs forever and the feedback bar shows a row count in the millions, and it doesn't stop running.

Below are two weblinks showcasing the same filter script I am having an issue with. My script is as follows.

Am I doing something wrong? Why is the query running forever and displaying millions of rows in the feedback bar? It's gotten as high as 24M before I cancelled the query.

myName = Table.SelectRows(fxCalc_MeterDateBusinessKey, each List.Contains(stage_SummaryTicket[MeterDateBusinessKey], [MeterDateBusinessKey]) = false)

Reference

Quick Tips: How to Filter a Column by another Column from a Different Query in Power Query

https://www.biinsight.com/quick-tips-how-to-filter-a-column-by-a-column-from-a-different-query-in-power-query/

#PowerQuery – Filter a table based on another table column or list – and some Filter aha’s – Erik Svensen – Blog about Power BI, Power Apps, Power Query

https://eriksvensen.wordpress.com/2017/12/12/powerquery-filter-a-table-based-on-another-table-column-or-list-and-some-filter-ahas/

Image

Answer 1

If you want to use PowerQuery to look for rows in Table2 that don't have matches in Table1 based on the MeterDateBusinessKey field

Load Table1 into powerquery

Load Table2 into powerquery ... Home .. Merge Queries...

Merge Table2 with Table1 using the MeterDateBusinessKey field (and any additional fields as well) and choose Left Anti as the type. Hit OK and the rows left are those that are in Table2 but not Table1

let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Merged Queries" = Table.NestedJoin(Source,{"MeterDateBusinessKey"},Table1,{"MeterDateBusinessKey"},"Table1",JoinKind.LeftAnti)
in #"Merged Queries"

Answer 2

I added List.Buffer. Then the query ran in about 5 seconds and the results look great!

myName = Table.SelectRows(fxCalc_MeterDateBusinessKey, each List.Contains( List.Buffer(stage_SummaryTicket[MeterDateBusinessKey] ), [custMeterDateBusinessKey]) = false)

As was suggested in this thread:

Solved: List.Contains() is slow - Microsoft Power BI Community

https://community.powerbi.com/t5/Desktop/List-Contains-is-slow/m-p/949905