When working with Power Query, it’s actually pretty simple to restrict records in a table using an AND criteria.  As filters on different columns are applied in a cumulative fashion, we just keep applying more to filter down the data.  But what about performing OR logic tests?

The issue

Assume we’ve got a list that looks like this:

And we want to preserve any record where the inventory item is Talkative Parrot OR the item was sold by Fred.  So how would you do that?

We’d be tempted to start by filtering the Inventory Item column to just the Talkative Parrot item.  But that would remove our very first row, which shows an item sold by Fred.  And if we started by filtering the Sold By column to Fred, we’d lose the Talkative Parrot in row 2.

Performing OR logic tests – via merging columns

We could merge the Inventory Item column together with the Sold By column, then filter for any item that contains Talkative Parrot or Fred.  While this might work in this situation, it could also lead to cross contamination if there were names like Manfred or Wilfred, or if the name we were searching for appeared in one of our Inventory items (like “Fred the dog”.)

Performing OR logic tests – via list functions

Using list functions is a far better way to make this kind of comparison. In fact, there is a function that is specifically built for this purpose, which works exactly like Excel’s OR() function; List.AnyTrue().

To use this, we’ll set up a new custom column with the following formula:

=List.AnyTrue(
{[Inventory Item]="Talkative Parrot",

[Sold By]="Fred"}
)

The formula breaks down as follows:

• The function name is List.AnyTrue()
• The function requires a list, which means that you need the {} inside the parenthesis.
• Each list item (comparison), is nested inside the curly braces, and is separated by commas

Knowing this, we then just need to include the comparisons we want to make, with the field name surrounded in square brackets, and the comparison surrounded in quotes (as we are making a text comparison here.)

And the result, as with Excel’s OR() function, is a list of TRUE/FALSE values:

Of course, we can do better than this, by wrapping the original function in an if statement:

=if List.AnyTrue(
{[Inventory Item]="Talkative Parrot",[Sold By]="Fred"}
)
then "Meets Criteria!"
else "No Match"

Which returns useful messages instead of just TRUE/FALSE values:

Naturally, you could filter these records or keep them, as your need dictates.

Performing AND logic tests

If you’re simply looking to filter out records based on an AND criteria, then applying successive filters will work.  But what if you only want to tag the records, so that you can use this field in a slicer?  In that case you need to preserve both the matching and non-matching items.

Fortunately, Power Query has a list function for this as well:  =List.AllTrue().

This function is created in the same manner as List.AnyTrue(), where the following would only show a “Meets Criteria!” message where the Inventory Item is Talkative Parrot AND the Sold By field contains “Fred”:

=if List.AllTrue(
{[Inventory Item]="Talkative Parrot",[Sold By]="Fred"}
)
then "Meets Criteria!"
else "No Match"

A Quick Note

This topic is contained in the forthcoming M is for Data Monkey book.  And it’s not too late to get YOUR name inside!