Filtering data with list inputs

Overview

Sometimes, you might need to filter data using a list of input criteria, but without hard-coding this list into the steps of ETL transformation itself.

For example, you might have the following data:

With the above data, you wish to filter out any rows, where the item description begins with “Trousers” or “Shorts”.

You would also like to maintain these filters, (‘Trousers’, and ‘Shorts’) in a separate list, like the one shown below:

This article explains how to do this!

Steps

1. Bring both tables into the Query Editor

• To start, bring the filtering list (called: ‘Exclude’ above) into the query editor, so it looks like this:

• Filter out any nulls using the filter button at the top.
• Next, click the fx button, so that the next step to the query looks like this:

    = #"Filtered Rows"[Exclude]

This will now turn the query, from a table into a list, like shown below:

• Call the query: filterList

• Next, bring in the table with the actual data into the query editor so it looks like this:

• Call this query: Data

2. Create a filter function

Now we need to find out a way of comparing each cell in the actual data (in the column called “Clothing”), with every item in the list, called: filterList. This kind of iterative comparison, is best achieved with a function:

• Create a blank query, with the following code

Details of the function are explained below:

• Parameters – the function accepts two arguments: one a record object (variable name rcd); the other, a field in the record object, (variable name field), that needs to be compared to each item in the filterList
• The List.Transform function compares each item in the filterList, with the value contained in the variable, field. If the value of the field item starts with the value of the item in the filterList, a TRUE will be returned, otherwise, a FALSE.
  • So, for example, if the field value is “Trousers-Short”, and the filterList is {Trousers, Shorts}, List.Transform, using the Text.StartsWith function will return {TRUE, FALSE}.
  • Similarly, if the field value is “T-Shirt-Medium”, the List.Transform function will return {FALSE, FALSE}
• The List.AnyTrue function completes things, by returning a TRUE if any item in the list returned by List.Transform is TRUE.
• The “_” in the Text.StartWith function is just a variable that refers to each item in the list, filterList

That’s it!

• Now, give the function the name: fnStartsWithFilter

3. Call the function

Now call the function to check that it returns the expected results.

• Do this by going back to the query called ‘Data’, and add the following custom column:

The “_” in the function above, is just a dummy variable that refers to the current record of the data, which is the first parameter needed by the function.

The function, after running, should return the following results, as an extra column, as shown below:

• Since we are excluding items, select “FALSE” under the column called Filter
• Then remove the Filter column, as it is no longer needed

The data should finally look like this:

4. Test the query

If you change the filter list - let’s say this time we want to exclude Trousers, and T-Shirts - and refresh the query again, you should then get the updated results, as shown below:

Perfecto!