PQ: Remove Group of rows for if just one of rows in the group have a specific value

0
votes

I have 2 columns. (Please see the linked image below)

Col 1 is Item ID

Col 2 is Warehouse ID

I want to remove all rows for a certain group Col1 (Item ID) if just 1 of the rows in the group has a value of either 20 or 50 in Col2 (Warehouse ID) Excel input and Power Query Output

1
powerquery

1 Answers

0
votes
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ItemId", type text}, {"WarehouseID ", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"ItemId"}, {{"AllRows", each _, type table [ItemId=text, #"WarehouseID "=number]}}),
#"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1),
#"Expanded AllRows" = Table.ExpandTableColumn(#"Added Index", "AllRows", {"WarehouseID "}, {"WarehouseID "}),
#"Added Conditional Column" = Table.AddColumn(#"Expanded AllRows", "Find ItemId", each if [#"WarehouseID "] = 20 then [ItemId] else if [#"WarehouseID "] = 50 then [ItemId] else null),
#"Merged Queries" = Table.NestedJoin(#"Added Conditional Column", {"ItemId"}, #"Find ItemId", {"Column1"}, "Find ItemId.1", JoinKind.LeftAnti),
#"Removed Other Columns" = Table.SelectColumns(#"Merged Queries",{"ItemId", "WarehouseID ", "Index"})

in #"Removed Other Columns"

let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ItemId", type text}, {"WarehouseID ", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"ItemId"}, {{"AllRows", each _, type table [ItemId=text, #"WarehouseID "=number]}}),
#"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1),
#"Expanded AllRows" = Table.ExpandTableColumn(#"Added Index", "AllRows", {"WarehouseID "}, {"WarehouseID "}),
#"Added Conditional Column" = Table.AddColumn(#"Expanded AllRows", "Find ItemId", each if [#"WarehouseID "] = 20 then [ItemId] else if [#"WarehouseID "] = 50 then [ItemId] else null),
#"Find ItemId1" = #"Added Conditional Column"[Find ItemId],
#"Removed Duplicates" = List.Distinct(#"Find ItemId1"),
#"Converted to Table" = Table.FromList(#"Removed Duplicates", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Filtered Rows" = Table.SelectRows(#"Converted to Table", each ([Column1] <> null))

in #"Filtered Rows"