How to Set Multiple Exclude Conditions with Words in a file instead of Wildcard Exclude Filter in Tableau Prep

Published: 18 Jan 2023
Last Modified Date: 19 Jan 2023

Question

How to Set Multiple Exclude Conditions with Words in a file (such as a excel file) instead of using Wildcard Exclude Filter in Tableau Prep Builder?
When you add all the exclusion words using Wildcard Exclude Filters one by one, you have to edit the Wildcard Exclude Filters manually every time we want to add new exclusion word or remove one.
Is there a simple way to add/remove exclusion words?

Environment

Tableau Prep Builder

Answer

Consider the target data is [Orders] sheet in "Sample - Superstore.xls" and we have 3 exclusion words in a file (such as an excel file named as "exclude_list.xlsx") like below. We want to exclude the records that have these words in the field [Order ID].
exclude_list.xlsx

word_list

2019

2021

2023

1. Create following calculation for both Table [Orders] and Table [exclude_list].
Calculated Field Name: dummy_for_join
Calculation:

2. Join Table [Orders] and Table [word_list] with [dummy_for_join]=[dummy_for_join], and Join Type [Inner].
3. Change the data type of Field [Order ID] in Table [Orders] and [word_list] in Table [exclude_list] as "String" if they are not.
4. Create following calculation for the join result.
Calculated Field Name: Is Exclude Target ?
Calculation:

CONTAINS([Order ID],[word_list])

5. Filter the join result by "True".
6. Keep only Field [Order ID] for the result of step 5.
7. Join Table [Orders] and the result of step 6 with [Order ID] =[Order ID], and Join Type [Left unmatched only].
8. Remove the duplicated Field [Order ID -1] from the join result of step 7.

An Example flow "Exclude_Words_in_a_file.tflx" has been attached to this article.