Exclude already used elements from the list

[Ruslan_Akiev]

Hello everyone! An indirect continuation of this post. The question is the following. There are two tables. The first (Table 1) table has one column filled with serial numbers (one entry per row). The second table (Table 2) has a column in which serial numbers from the first table are written, separated by commas, using EnumList and IN. I can’t figure out how to make it so that when adding serial numbers again, those numbers that have already been added are not offered.

I tried options with subtracting a list from a list, I also tried the NOT CONTAINS function, but it does not work. It turns out that in my "Table 2" each line with serial numbers is a small list of several values ​​separated by commas. There are many such rows and they all need to be subtracted from the column in "Table 1".

Table 2

Table 1

 

[WillowMobileSys · Accepted solution]

The problem you are likely facing is getting the complete list assigned Serial Numbers from the EnumList column.  When selecting that column from Table2 you are returned a List of LIsts.  It needs to be flattened to just a single list.

Try an expression like this (replace the table and column names for your use case):

SELECT(Table1[Serial Number], NOT(IN([Serial Number], Split(Text(Table2[Serial Numbers]), ","))))

Also, if you are allowing Editing of your Table 2 rows, you will probably need a slightly modified version that will still include the values from the current row.  That expression needs to be something like this:

SELECT(Table1[Serial Number], 
       OR(
          NOT(IN([Serial Number], Split(Text(Table2[Serial Numbers]), ","))),
          IN(Serial Number, [_THISROW].[Serial Numbers]
       )
)

NOTE:  These have not been tested.

[Ruslan_Akiev]

Thanks for the advice! I'll check it out soon. By the way, I am now constantly confused by the SELECT function because of its possible performance impact.

[WillowMobileSys]

Performance is typically not an issue if you follow best practices.  I wouldn't be concerned unless you have tens of thousands of rows or need to implement something extraordinarily complex.  If you do, come back here!! 

[Ruslan_Akiev]

I tested the solution you suggested and it worked for me 😀

Thanks a lot for your help!