Filter Table based on Condition from Other Table Column

[joshs444]

I have two tables:

Current Inventory:

Container ETA:

I would like to filter out values in "Container ETA" that are the have the same "Load Name" as "Load" from "Current Inventory" table.

So if there is a load name in "Container ETA" that is also in "Current Inventory", I would like all rows removed from "Container ETA" with the that "Load" removed. 

I am not too farmiliar with FILTER functions, but it seems like this shouldnt be too difficult.

All help is appreciated!

Thanks!

[tamerj1 · Accepted solution]

@joshs444 
You can place the following measure in the filter pane of the visual and select "is not blank" then apply the filter

FilterMeasure =
VAR InventoryLoads =
    VALUES ( 'Current Inventory'[Load] )
RETURN
    COUNTROWS (
        FILTER ( 'Container ETA', NOT ( 'Container ETA'[Load Name] IN InventoryLoads ) )
    )

[joshs444]

This seems like the best solution. Create a calculated column and then sort in power query to remove values in both tables.

Thanks!

[v-yiruan-msft · Accepted solution]

Hi @joshs444 ,

If you only want to get the Load Names in the table 'Container ETA' which also exist in the table 'Current Inventory', you can create a calculated column as below in the table 'Container ETA' to get it.

Column = 
CALCULATE (
    MAX ( 'Current Inventory'[LOAD] ),
    FILTER (
        'Current Inventory',
        'Current Inventory'[LOAD] = EARLIER ( 'Container ETA'[Load Name] )
    )
)

And if you want to remove the rows in the table 'Container ETA' which exist the same Load Name with the LOAD in the table 'Current Inventory', you can follow the below steps to get it in Power Query Editor...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldA7EsIwDATQu7jOTLSrT5wb0FBThByD+2NBkxQYp5LG8rMkb1t5mIeUqXAmZgq15bfnS4Rxb6lBkdXPnbJP/4ExgUo9guU3QIi14OcOPeAV18CKyMA4AO+AVZhvQ05L9EStkSd0GQRWLZeIhYMAwRyGtNEOCs8OdhhJ0QFOzQ8KfEfa3w==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Load Name" = _t, #"ETA to Port" = _t, Warehouse = _t, #"Weruva SKU" = _t, Quantity = _t, #"Delivery date to H&M" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Load Name", type text}, {"ETA to Port", type date}, {"Warehouse", type text}, {"Weruva SKU", Int64.Type}, {"Quantity", Int64.Type}, {"Delivery date to H&M", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Load Name"}, #"Current Inventory", {"LOAD"}, "Current Inventory", JoinKind.LeftOuter),
    #"Expanded Current Inventory" = Table.ExpandTableColumn(#"Merged Queries", "Current Inventory", {"LOAD"}, {"LOAD"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Current Inventory", each ([LOAD] = null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"LOAD"})
in
    #"Removed Columns"

Best Regards

[joshs444]

Ideally I would filter the initial Container ETAs table, but if that doesnt work then this is a great solution.

Thanks!

[tamerj1 · Accepted solution]

@joshs444 
You can place the following measure in the filter pane of the visual and select "is not blank" then apply the filter

FilterMeasure =
VAR InventoryLoads =
    VALUES ( 'Current Inventory'[Load] )
RETURN
    COUNTROWS (
        FILTER ( 'Container ETA', NOT ( 'Container ETA'[Load Name] IN InventoryLoads ) )
    )

[tamerj1]

@joshs444 

So would you like to have a new calculated table?

Table =
VAR InventoryLoads =
VALUES ( 'Current Inventory'[Load] )
RETURN
FILTER ( 'Container ETA', NOT ( 'Container ETA'[Load Name] IN InventoryLoads ) )