Solved: Exclude already used elements from the list - Page 2

Ruslan_Akiev · 02-05-2022 02:03 PM

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

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.

View solution in original post