Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
joshs444
Frequent Visitor

Filter Table based on Condition from Other Table Column

I have two tables:

 

Current Inventory:

joshs444_2-1674323231982.png

 

Container ETA:

 

joshs444_1-1674323183107.png

 

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!

2 ACCEPTED SOLUTIONS

@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 ) )
    )

View solution in original post

v-yiruan-msft
Community Support
Community Support

Hi @joshs444 ,

If you only want to get the Load Names in the table 'Container ETAwhich 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] )
    )
)

yingyinr_0-1674541751483.png

 

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"

yingyinr_1-1674541964797.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
joshs444
Frequent Visitor

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
Community Support
Community Support

Hi @joshs444 ,

If you only want to get the Load Names in the table 'Container ETAwhich 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] )
    )
)

yingyinr_0-1674541751483.png

 

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"

yingyinr_1-1674541964797.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
joshs444
Frequent Visitor

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


Thanks!

@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
Super User
Super User

@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 ) )

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors