Power Query - Filter List Based On Group

[LarsAustin]

Hi All,

I need help.

I have the following data in Power Query:

Invoice No.	Item No.	Group
IN0838602	33853	Non-Commodity
IN0838602	11464	Non-Commodity
IN0838607	37301	Non-Commodity
IN0838607	11472	Non-Commodity
IN0838607	12903	Non-Commodity
IN0838608	11810	Commodity
IN0838608	11439	Non-Commodity
IN0838608	13132	Commodity
IN0838609	11430	Non-Commodity
IN0838609	11736	Non-Commodity
IN0838609	11464	Non-Commodity
IN0838609	11472	Non-Commodity
IN0838609	51938	Commodity
IN0838616	11481	Non-Commodity
IN0838616	11497	Non-Commodity
IN0838616	11739	Non-Commodity
IN0838616	51637	Commodity
IN0838617	51995	Commodity
IN0838617	11481	Non-Commodity
IN0838617	11738	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

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.

The desired output will be like the one below:

Invoice No.	Item No.	Group
IN0838608	11810	Commodity
IN0838608	11439	Non-Commodity
IN0838608	13132	Commodity
IN0838609	11430	Non-Commodity
IN0838609	11736	Non-Commodity
IN0838609	11464	Non-Commodity
IN0838609	11472	Non-Commodity
IN0838609	51938	Commodity
IN0838616	11481	Non-Commodity
IN0838616	11497	Non-Commodity
IN0838616	11739	Non-Commodity
IN0838616	51637	Commodity
IN0838617	51995	Commodity
IN0838617	11481	Non-Commodity
IN0838617	11738	Non-Commodity
IN0838625	53071	Commodity
IN0838625	51355	Commodity
IN0838625	51637	Commodity
IN0838625	112433	Non-Commodity
IN0838625	12298	Non-Commodity

Something in my head is telling me that this is a simple fix but I just cannot figure it out. Your help will be much appreciated.

Thank you

Lars

 

[I_cant_read]

Try to load the data into two queries using the same table.

1.)Make a Query which is group by "Advanced" columns [Invoice No.] and [Group] > then filter column [Group] "Commodity".
This in turn should return you a list of Invoices which has commodity in [Group] column.

2.)Make a Query which loads your original table.

3.)Merge Query1 and Query2 using left outer. Then expand the table which is returned during the merge. Tadaa!

This should return the desired output.

 

[LarsAustin]

Thank you so much!

Lars