PQ - Filter List Based On Group

(question from the web)

Quote:

---

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.	Group		Invoice No.	Item No.	Group
IN0838602	33853	Non-Commodity		IN0838608	11810	Commodity
IN0838602	11464	Non-Commodity		IN0838608	11439	Non-Commodity
IN0838607	37301	Non-Commodity		IN0838608	13132	Commodity
IN0838607	11472	Non-Commodity		IN0838609	11430	Non-Commodity
IN0838607	12903	Non-Commodity		IN0838609	11736	Non-Commodity
IN0838608	11810	Commodity		IN0838609	11464	Non-Commodity
IN0838608	11439	Non-Commodity		IN0838609	11472	Non-Commodity
IN0838608	13132	Commodity		IN0838609	51938	Commodity
IN0838609	11430	Non-Commodity		IN0838616	11481	Non-Commodity
IN0838609	11736	Non-Commodity		IN0838616	11497	Non-Commodity
IN0838609	11464	Non-Commodity		IN0838616	11739	Non-Commodity
IN0838609	11472	Non-Commodity		IN0838616	51637	Commodity
IN0838609	51938	Commodity		IN0838617	51995	Commodity
IN0838616	11481	Non-Commodity		IN0838617	11481	Non-Commodity
IN0838616	11497	Non-Commodity		IN0838617	11738	Non-Commodity
IN0838616	11739	Non-Commodity		IN0838625	53071	Commodity
IN0838616	51637	Commodity		IN0838625	51355	Commodity
IN0838617	51995	Commodity		IN0838625	51637	Commodity
IN0838617	11481	Non-Commodity		IN0838625	112433	Non-Commodity
IN0838617	11738	Non-Commodity		IN0838625	12298	Non-Commodity
IN0838625	53071	Commodity
IN0838625	51355	Commodity
IN0838625	51637	Commodity
IN0838625	112433	Non-Commodity
IN0838625	12298	Non-Commodity
IN0838629	51460	Non-Commodity
IN0838629	12639	Non-Commodity
IN0838635	51938	Non-Commodity
IN0838635	52972	Non-Commodity
IN0838635	720644	Non-Commodity

Code:

---

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

---