Building the Filtering via Power Query<\/strong><\/p>\n

\n
1. Add or create the list\/table that will ultimately be filtered.<\/li>\n
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).<\/li>\n
3. In the Power Query window, click the Add Column menu, and select Custom Column.<\/li>\n
4. In the window that opens, type this:
   \n

   \n

   = Table.SelectRows(#”Changed Type”, each List.Count (Splitter.SplitTextByAnyDelimiter(NoCriteria)([PRODUCT_NAME]))=1)<\/strong><\/p>\n<\/blockquote>\n<\/li>\n

   Change [PRODUCT_NAME] in that text to your own column in the table that you will be filtering on if it is different.<\/li>\n

   Select the statement you have typed in and copy it (you will need to paste this formula in a following step).<\/li>\n

   Click OK. You will see that the formula you typed was changed by the program and a column was added.<\/li>\n

   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.<\/li>\n

   The extra column should be removed and the table should be filtered on you criteria from the NoCriteria table.<\/li>\n

   Click the File menu, choose Close and Load To, then choose where you would like the newly filtered table loaded to.<\/li>\n<\/ol>\n

   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<\/strong>.<\/p>\n

   \u00a0<\/p>\n

   Bonus – Filtered table with the excluded items only (not shown in video)<\/strong><\/span><\/p>\n

   You can additionally create a filtered table that only includes the terms in your NoCriteria table!\u00a0\u00a0<\/p>\n

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

      \n

      = Table.SelectRows(#”Changed Type”, each List.Count (Splitter.SplitTextByAnyDelimiter(NoCriteria)([PRODUCT_NAME]))<>1<\/strong><\/span>)<\/p>\n<\/blockquote>\n<\/li>\n

      Hit enter.\u00a0 The table should now only filter on the items in your exclusion list, instead of including them.<\/li>\n

      Click the File menu, choose Close and Load To, then choose where you would like the newly filtered table loaded to.<\/li>\n<\/ol>\n<\/div>\n