Forum Discussion

Tommyvhod's avatar
Tommyvhod
Icon for Helper II rankHelper II
6 years ago
Solved

Group withouth duplicates

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's avatar
    AlB
    6 years ago

    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  Datanaut

1 Reply

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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  Datanaut