Filtering by a column in another table slows down peformance

[jcbutts]

Hi,

I am trying to filter a column of item ID's by those found in a column in another table.  I used the following M code to filter:

= Table.SelectRows(#"Renamed Columns2", each List.Contains(#"Forecast"[Item ID],[Master Item ID List]))

This seems to filter the existing "master item ID list" column appropriately, but then my file runs incredibly slow.  Applying any changes from this query takes like 10 minutes, whereas before this step it was 10 seconds.  I tried adding "Table.Buffer" to my source line in the advanced editor.  Any other thoughts to speed this up?  Should I be filtering a different way?

[V-lianl-msft]

Hi @jcbutts ,

It may be a good choice to transform the data before loading it, or to establish a relationship between two tables.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jcbutts]

Hi V,

I do have a relationship established between each table and a "master table" that has all possible item ID's.  So think of each table as having a subset of those ID's.  When I'm trying to create visuals, I need to filter by the smaller of the two datasets, or I get a bunch of blanks and other errant data in the visual.

Is there another way to accomplish this without filtering the larger table by the smaller table in query editor?

[kentyler]

Have you tried doing the filtering in the source, before loading the table?

[jcbutts]

I don't want to filter the source itself, as it may be a fluid task that I don't want to constantly keep updating.  At first, I was hopeful that I could simply filter via the visualizations in the desktop, but nothing worked.  Filtering in the query editor gets me the right visuals, it just takes a long time to load.