Forum Discussion

jcbutts's avatar
jcbutts
Helper I
6 years ago

Filtering by a column in another table slows down peformance

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?

Need Help

4 Replies

  • kentyler's avatar
    kentyler
    Solution Sage
    6 years ago

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

    • jcbutts's avatar
      jcbutts
      Helper I
      6 years ago

      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.

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    6 years ago
    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's avatar
      jcbutts
      Helper I
      6 years ago

      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?