1. Create a new, separate table with the list of terms you will want to exclude. Name the table “NoCriteria”.
2. Add the excluded item (NoCriteria) table to Power Query – click within table, under Data menu, choose From Table/Range, which is in the Get and Transform data section.
3. In the Power Query window, select the Transform menu and click convert to list.
4. Under the File menu, choose Close and Load To, then choose Connection Only.

Building the Filtering via Power Query

1. Add or create the list/table that will ultimately be filtered.
2. Click any cell within the table that will be filtered. Add the excluded item table to Power Query (click within table, under Data menu, choose From Table/Range, which is in the Get and Transform data section).
3. In the Power Query window, click the Add Column menu, and select Custom Column.
4. In the window that opens, type this:
   

   = Table.SelectRows(#”Changed Type”, each List.Count (Splitter.SplitTextByAnyDelimiter(NoCriteria)([PRODUCT_NAME]))=1)

5. Change [PRODUCT_NAME] in that text to your own column in the table that you will be filtering on if it is different.
6. Select the statement you have typed in and copy it (you will need to paste this formula in a following step).
7. Click OK. You will see that the formula you typed was changed by the program and a column was added.
8. To change the formula back, click the menu bar, highlight the entire text and then replace by pasting in the formula you copied. Hit enter.
9. The extra column should be removed and the table should be filtered on you criteria from the NoCriteria table.
10. Click the File menu, choose Close and Load To, then choose where you would like the newly filtered table loaded to.

Any time changes are made to the exclusion list, you will need to refresh the filtered table. Simply right click any cell within the filtered table, and select Refresh.

Bonus – Filtered table with the excluded items only (not shown in video)

You can additionally create a filtered table that only includes the terms in your NoCriteria table!  

1. To do this, go into Power Query.  Right click on your filtered table and click Duplicate.
2. In that new table, you will very slightly change the existing formula in your Power Query to not equal one (see orange text):
   

   = Table.SelectRows(#”Changed Type”, each List.Count (Splitter.SplitTextByAnyDelimiter(NoCriteria)([PRODUCT_NAME]))<>1)

3. Hit enter.  The table should now only filter on the items in your exclusion list, instead of including them.
4. Click the File menu, choose Close and Load To, then choose where you would like the newly filtered table loaded to.