Group withouth duplicates

[Tommyvhod]

Hi all. I would like to create a list called WIP. I have 3 querys - all IDs, than another called warehouse and one shipped. I would like to remove the warehouse and the sipped parts ID from the "all ID" list to get the production parts.E.g:

IDs , 11,22,23,25,27,28,30,33,35  Warehouse: 28,27,25,   Shipped: 23,22,11  = WIP : 30,33,35  (a-b-c=d)

What would be the best method?

Thank you in advance

[AlB · Accepted solution]

Hi @Tommyvhod 

You can do that with EXCEPT( ) and UNION( ). Assuming ID, Warehouse and Shipped are tables:

1. All columns must have the same name, so rename, for example,  to ID[ID], Warehouse[ID] and Shipped[ID]

2. Create a new calculated table (one column with your list):

WIP =
EXCEPT (
    DISTINCT ( ID[ID] ),
    UNION ( DISTINCT ( Warehouse[ID] ), DISTINCT ( Shipped[ID] ) )
)

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers