Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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 ) )
)
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
This seems like the best solution. Create a calculated column and then sort in power query to remove values in both tables.
Thanks!
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
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 ) )
)
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 ) )
User | Count |
---|---|
47 | |
44 | |
19 | |
14 | |
14 |
User | Count |
---|---|
107 | |
55 | |
28 | |
20 | |
15 |