Normalizing data with the ternary operator

Introduction

The ternary operator is an inline if-then-else statement that can be used to replace values within a column. Ternary operators are commonly used with the let statement.

let column = (conditional) ? value-if-true : value-if-false

Where

(conditional) is a DataSet search query that evaluates to a result of true or false. For example,

(pet='finch')

and

value-if-true and value-if-false are string or integer values (or a statement that evaluates to a string or integer value)

Optimizing the Conditional

What if there's a number of different values? For example,

| pet      |
| -------- |
| cat      |
| Cat      |
| cats     |
| dog      |
| Dog      |
| dogs     |
| finch    |
| Finch    |
| finches  |
| Pangolin |

It wouldn't make sense to use a separate conditional for each variation. However, the PowerQuery syntax is quite versatile. Rather than a conditional of:

(pet='Dog' || pet='dog' || pet='dogs')

We can use a regular expression within a single conditional to match all three cases:

(pet matches '[dD]ogs?')

Multiple Conditional Statements

For scenarios where multiple values need to be matched, ternary operators can be stacked together:

let normalized_pet = (pet matches '[dD]ogs?') ? "dog" : (pet matches '[cC]ats?') ? "cat" : (pet matches '[fF]inch(es)?') ? "finch" : pet

Example

Given the "pet" dataset above, we use a conditional statement to normalize the value of the "pet" column. A default value of "Not_A_Pet" is applied when the content of the "pet" column does not match any of the conditionals.

logfile='/var/log/pq_request.log'
| columns pet
| let normalized_pet = (pet matches '[dD]ogs?') ? "dog" : (pet matches '[cC]ats?') ? "cat" : (pet matches '[fF]inch(es)?') ? "finch" : "Not_A_Pet"

This assigns a category to the normalized_pet column:

| pet      | normalized_pet |
| -------- | -------------- |
| dog      | dog            |
| dogs     | dog            |
| Dog      | dog            |
| cat      | cat            |
| cats     | cat            |
| Cat      | cat            |
| finch    | finch          |
| Finch    | finch          |
| finches  | finch          |
| Pangolin | Not_A_Pet      |

Consequently, when using a group by statement, the results will be counted according to the category, as irregularities in capitalization / spelling have been removed:

logfile='/var/log/pq_request.log'
| let normalized_pet = (pet matches '[dD]ogs?') ? "dog" : (pet matches '[cC]ats?') ? "cat" : (pet matches '[fF]inch(es)?') ? "finch" : "Not_A_Pet" 
| group total=count() by normalized_pet

| normalized_pet | total |
| -------------- | ----- |
| cat            | 3     |
| dog            | 3     |
| finch          | 3     |
| Not_A_Pet      | 1     |

Conclusion

As you can see, the ternary statement can process conditionals in sequence, and has behavior similar to a CASE statement (or multiple if-then-else statements). This makes the ternary statement especially useful when normalizing the values associated with an attribute.