Applying OR, AND, and SWITCH logic

Overview

In this article, we explain how OR, AND, and SWITCH logic, can be built into ETL data queries.

Multicolumn Logic

Often, data needs to be filtered based on multiple criteria.

Consider the following data, as an example:

The OR ( ) function

If your boss asked you to list only data where you sold a Talkative Parrot or items sold by Fred, you might be tempted to:

• Just filter the Inventory Item column to Talkative Parrot and then filter the Sold By column to Fred.

But this will not work because you will lose any Talkative Parrots sold by John or Jane, and also lose any other items sold by Fred.

If the data is stored in an Excel table, you could solve this problem by using Advanced Filter, or by adding a column using the following formula, and filtering to only results that are listed as true:

    =OR([@[Inventory Item]]=“Talkative Parrot”,[@[Sold By]]=“Fred”)

But if the data does not originate in an Excel table, or if it is sourced from a database, a web page, or a text file, this will not work.

What follows are some ways of solving this proble with an ETL.

1. Add a Custom Column

Pull the data into Power Query:

• Select any cell in the table on the worksheet
• Create a new query ➔ From Table
• Right-click the Date column ➔ Change Type ➔ Date

Since you cannot filter your data without losing required records, you will need to add a custom column and apply a formula to examine each row:

• Go to Add Column ➔ Add Custom Column
• Name the column ‘Match?’

Next, test whether a certain criterion is true or false, using the following logic framework:

   = if logic_test then “Meets Criteria!” else “No Match”

When looking for text comparisons, it can be helpful to quickly scan the list of list functions contained in the Power Query formula categories.

Within the list functions, there is a ‘List.AnyTrue( )’ function. Selecting that item in the documentation reveals the following example:

   List.AnyTrue({2=0, false, 1 < 0 }) equals false

Based on this, the function contains a list of values, as indicated by the curly braces within the parentheses, and will return false, only if none of the items are true.

• So, as a next step, use this formula in place of the logic test, like shown here:

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

When you click OK, the formula should return a ‘Meets Criteria’ message where the ‘Inventory item’ equals Talkative Parrot, or the ‘Sold By’ field holds Fred:

Since the function returns true if any of the criteria are true, any instances of where Fred sold a Talkative Parrot would also display the result ‘Meets Criteria!’ in the ‘Match?’ column.

You can now finalize this query by taking the following steps:

• Filter the ‘Match?’ column to only include only ‘Meets Criteria!’ Values
• Go to Home ➔ Close and Load

The AND ( ) function

Although you can reduce records by filtering a table column-by-column, gradually reducing the data, if you only want to label records that meet multiple criteria (and not filter them out), you need to use AND ( ) logic.

Here, instead of replicating the OR ( ) function with ‘List.AnyTrue( )’, to use AND ( ) logic, you need to use the ‘List.AllTrue( )’ function. This function returns a true value only if every logical test provided returns a true value.

With this function you can easily label records without filtering them first. This gives you more flexibility in how you build your logic, while still preserving the original data — something that cannot be done if you filter columns to drill into the end values.

The SWITCH ( ) function

Excel also has a function called SWITCH( ) that allows you to perform multi-condition logic by declaring a table of index values and results, and then passing these into a variable value. The function then looks up the provided index value and returns the matching result. As such an approach is easier to maintain than having several levels of nested IF( ) statements, it sometimes makes sense to use SWITCH( ) based logic.

The syntax for this function is as follows:

    = SWITCH(expression,value_1,result_1,[value_2,result_2],…,[Else])

One example of where this can be really useful is when breaking down encoded patterns like customer billing codes, where each character represents something specific. Take a code like the MP010450SP, where the ninth character could be one of the following:

    E = Employee, S = Yacht Club, N = Non-Taxable, R = Restricted,

    I = Inactive, L = Social, M = Medical, U = Regular

To break this apart in Excel, you could build a function with many nested IF statements and build a VLOOKUP( ) based on the letter options. This can instead be done with the SWITCH( ) function, as follows:

    = SWITCH([Column],“E”,“Employee”,“S”,“Yacht Club”,

      “N”,“Non-Taxable”,“R”,“Restricted”,“I”,“Inactive”,

      “L”,“Social”,“M”,“Medical”,“U”,“Regular”,“Undefined”)

1. Build a custom function

Building the function, in Power Query, is not overly difficult once you know the basic structure. Starting with the following Excel data table:

• Create a new query ➔ From Other Sources ➔ Blank Query
• Name the query fnSWITCH
• Go to Home ➔ Advanced Editor
• Enter the M code shown below:

    (input) =>
        let
            values = {
            {result_1, return_value_1},
            {input, “Undefined”}
            },
        Result = List.First(List.Select(values, each _{0}=input)){1} in
    Result

This code is the basic framework for any SWITCH( ) function. The key parts here are:

• result_1 is the first of the possibilities passed to the function
• return_value_1 is the value returned if the first value is result_1
• If more values are needed, another comma can be inserted after the {result_1, return_value_1} section and the following {result_2, return_value_2} included
• As many values can be kept as needed
• The input value in the list will return the text ‘Undefined’ if the value passed in isn’t in the provided list of options (it is the Else portion of the SWITCH( ) statement)

Using this structure, the fnSWITCH function can be modified for the scenario as follows:

(input) =>
    let
        values = {
        {“E”, “Employee”},
        {“S”, “SCYC”},
        {“N”, “Non-Taxable”},
        {“R”, “Restricted”},
        {“I”, “Inactive”},
        {“L”, “Social”},
        {“M”, “Medical”},
        {“U”, “Regular”},
        {input, “Undefined”}
        },
    Result = List.First(List.Select(values, each _{0}=input)){1} in
Result

The changes made here were simply to replace value_1 with “E”, and return_value_1 with “Employee”, and then to add more pairs of potential inputs and desired return values.

You are not restricted to looking up single characters - longer text strings can be looked up just as easily, so long as the options are entered in pairs between curly braces and have a comma at the end of each pair.

When the function has been fully modified:

• Click Done
• Go to Home ➔ Close & Load

2. Use the fnSwitch function

With the fnSWITCH( ) function created, it can be used to extract the billing type from each customer record in the dataset.

Load the billing data into Power Query

• Select any cell in the table on the Customers table (shown earlier) ➔ create a new query ➔ From Table / Range icon

We want the function to take the ninth character from the ‘BillingCode’ field and provide the corresponding customer type. To use it, you need to extract that character:

• Go to Add Column → Add Custom Column
• Name the column Customer Type
• Enter the following formula:

   =fnSwitch(Text.Range([BillingCode],8,1))

The results should look as follows:

All the sample codes in the data should work just fine - nothing comes back as undefined.

Now, follow these steps to see how the query reacts to different items:

• Change the query name to Billing
• Go to Home ➔ Close & Load
• Change the second-to-last letter of any billing code to something else

When you refresh the table, the function should evaluate the new character and returns the appropriate result.

As the function, as written above, is case sensitive, the code MP010450uP would return Undefined, even though MP010450UP is a valid code.

If you want to accept either uppercase or lowercase, the Billing query should to be modified to force the results of the Text.Range function to uppercase:

• Right-click the Billing query in the Workbook Queries pane ➔ Edit
• Select the Added Custom step ➔ click the gear icon
• Update the formula to read:

    = fnSwitch(Text.Upper(Text.Range([BillingCode],8,1)))

• Click OK
• Go to Home ➔ Close & Load

As shown below, this adjustment allows lowercase values to be passed into the function and still get a positive result: