PQ - Filter List Based On Group

[sandy666]

(question from the web (https://www.mrexcel.com/board/threads/power-query-filter-list-based-on-group.1161751/))

What I am trying to do is filter only the Invoice No. (first column) in which one or more items has Commodity as Group - but I need to include the Item No. in the output even if the corresponding group is non-commodity. Basically, I want to remove the invoice numbers in which the items under it are non-commodity.


Invoice No.Item No.GroupInvoice No.Item No.Group

IN0838602
33853Non-CommodityIN083860811810Commodity

IN0838602
11464Non-CommodityIN083860811439Non-Commodity

IN0838607
37301Non-CommodityIN083860813132Commodity

IN0838607
11472Non-CommodityIN083860911430Non-Commodity

IN0838607
12903Non-CommodityIN083860911736Non-Commodity

IN0838608
11810CommodityIN083860911464Non-Commodity

IN0838608
11439Non-CommodityIN083860911472Non-Commodity

IN0838608
13132CommodityIN083860951938Commodity

IN0838609
11430Non-CommodityIN083861611481Non-Commodity

IN0838609
11736Non-CommodityIN083861611497Non-Commodity

IN0838609
11464Non-CommodityIN083861611739Non-Commodity

IN0838609
11472Non-CommodityIN083861651637Commodity

IN0838609
51938CommodityIN083861751995Commodity

IN0838616
11481Non-CommodityIN083861711481Non-Commodity

IN0838616
11497Non-CommodityIN083861711738Non-Commodity

IN0838616
11739Non-CommodityIN083862553071Commodity

IN0838616
51637CommodityIN083862551355Commodity

IN0838617
51995CommodityIN083862551637Commodity

IN0838617
11481Non-CommodityIN0838625112433Non-Commodity

IN0838617
11738Non-CommodityIN083862512298Non-Commodity

IN0838625
53071Commodity

IN0838625
51355Commodity

IN0838625
51637Commodity

IN0838625
112433Non-Commodity

IN0838625
12298Non-Commodity

IN0838629
51460Non-Commodity

IN0838629
12639Non-Commodity

IN0838635
51938Non-Commodity

IN0838635
52972Non-Commodity

IN0838635
720644Non-Commodity


let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
GL = Table.Group(Source, {"Invoice No.", "Group"}, {{"Count", each _, type table}}, GroupKind.Local),
KD = let columnNames = {"Invoice No."}, addCount = Table.Group(GL, columnNames, {{"Count.1", Table.RowCount, type number}}), selectDuplicates = Table.SelectRows(addCount, each [Count.1] > 1), removeCount = Table.RemoveColumns(selectDuplicates, "Count.1") in Table.Join(GL, columnNames, removeCount, columnNames, JoinKind.Inner),
ETC = Table.ExpandTableColumn(KD, "Count", {"Item No."}),
TSC = Table.SelectColumns(ETC,{"Invoice No.", "Item No.", "Group"})
in
TSC