Forum Discussion

Milagros's avatar
Milagros
Helper I
4 years ago

Table Function, Filter table for values NOT in another table

Hi there, I have two tables. Tabe 1 All reference numbers, Table 2 Some reference numbers. I am trying to filter Table 1 to show only those reference numbers that do not appear in table 2. I tried this but with no luck. After typing the NOT operator, I do not see Table 2 at all.  Is there something wrong with the syntax?  I created a many to many relationship between the Reference number fields in Table 1 and Table 2, but that didn't help.

I want to keep it as simple as possible is there a better way of doing this?

 

Not in Table 2= FILTER(Table 1,Table 1[referenceNumber],NOT(xxx
 
Thank you
Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Milagros Try something like this:

    Not in Table 2=var tempFilter=EXCEPT(VALUES(Table 1[referenceNumber]),VALUES(Table 2[referenceNumber]))

    return FILTER(Table 1,Table 1[referenceNumber] in tempFilter)

    • Milagros's avatar
      Milagros
      Helper I
      4 years ago

      Hi Marik, Thank you I tried the suggestion but got this message.  "Cannot convert value 'WAL-105221-1' of type Text to type True/False." Maybe Except doesn't play nicely with text values?

    • Milagros's avatar
      Milagros
      Helper I
      4 years ago

      Hi Jay, Thanks, I tried that, but got this message "Each table argument of 'EXCEPT' must have the same number of columns." I should mention that my tables have other columns in them and they don't have the same number of columns.

      Kind regards, Mila