How to Avoid Case Sensitivity Trap in Power BI Conditional Columns

BySteve Young Updated on

Nesting IF statements are fairly common in Excel, but you can also use them in Power BI. There is an issue if you get into Conditional Columns because of Case Sensitivity. The same formula in DAX Calculated Columns in Power BI and the same formula in Power Query Conditional Columns gives different answers.

There are two ways to solve this, but the first is an interesting example you can use in several situations. This solution plays on the true-false values of the DAX IF function in PowerBI.

The Situation

I needed to quickly create a calculation that tested for the presence of a string in another column’s text values. I created the nested IF, as you would in DAX statements BUT used them in Power Query (Option 2 below).

Note: Showing Option 1 first, as I used this as my solution.

In Option 1, if you next IFs, as in the calculated column in Power BI DAX, it is not case sensitive. I liked this because it is very Excel like. People coming from Excel would probably start there. Option 2 shows an issue with this logic if you do this in Power Query. There is an editing fix to the PowerQuery formula in the Add Calculated Column feature by adding the M Function to Power Query Text.Lower.

The calculation would process a column based on the string containing a specific value. For example, “Jim’s Product” would be classified as a “Product,” and “Cleaning Service” would be classified as a service. Logic would be processed on what group the values belonged to. I was creating a custom column in PowerBI Desktop and putting an if statement containing a DAX search function in the conditional portion.

The Problem with Conditional Columns in Power Query!!! So, I thought that if the field containing the word “Product,” the Conditional Columns would evaluate to be TRUE and place “Product” in the column. The same is true for the word “Service.” The problem is that the condition fails if the case is different in the search value is the only value in the column.

Original Data

Original Data

Option 1: Nested If Statements

The below Power BI Calculated Column formula works, which blew me away. The IF condition would get the numeric location from the search function finding the first instance of the text it was looking for. This position could be any number except zero.

The DAX search function is below. The documentation states, “You can use the SEARCH function to determine the location of a character or text string within another text string.” This is not case-sensitive.

SEARCH(<find_text>, <within_text>[, [<start_num>][, <NotFoundValue>]])

This should not work, but it does. The picture below evaluates what you would expect; that zero is false.

But as the below screen capture shows, any number not equaling zero evaluates to true. So 22 is true. 🙂

So, you should be able to put in any function in the conditional portion of the IF statement that becomes a number. In this example, the DAX Search function gives you a numerical result.

Lesson Learned

Breaking one of my golden rules, I tried to do everything at once.

Golden Rule #1 – When you have a complex formula, write it in segments to break it up and make it easier to debug. Once you have it working, then you can get more complex.

Once I started looking at the calculation, I remembered that this functionality was in the Query Editor. Creating a Conditional Column for the grouping made writing the final calculation easier. Below are the steps to set this up.

Option 2: Using the Query Editor and Conditional Columns for Groupings

Pictured below is the screen that captures setting this up in PowerBI.

  1. Open the Query Editor.
  2. Select the Query you wish to edit.
  3. Select Conditional Column from the Add Column menu bar, which brings up the entry tab.
  4. Fill out the rule that matches your conditions.
  5. Add Rules until all your conditions are met.
  6. Don’t forget to put in a value for Otherwise to capture any values that do not fall under your conditions.

You will notice that rows 9,10,11,12 do not evaluate.

Conditional Column Not Working

You then have a grouping column that you can use in your formulas, as pictured above, but you notice that where cases are different, it does not work.

Once you have the column entered, you can edit the formula and fix things. Editing the formula, and adding the M Function to Power Query Text.Lower. Returns the result of converting all characters in text to lowercase.

Text.Lower(text as nullable text, optional culture as nullable text) as nullable text
Adding in a fix to the conditional formatting.

As you see below, the table is correct for all the values. Note that the search text is also put in lowercase. If you were using different columns or variables, you could also use Text.Lower to get a common case.

As with Option 1, if you next IFs, as in the calculated column in Power BI as a calculated measure, it is not case sensitive.

Wrap-up

Sometimes when you make a mistake, you can get some interesting results. For example, trying to get a formula to work and deciding to break it up showed me that it was a grouping task, and I could solve that in the Query Editor, making the final calculation far easier. However, I also learned something new that I can use elsewhere.

The source files and sample book are available on my GitHub repo.

With over 34 years of experience in the data and technology industry, the last 16 with Microsoft, I have had the opportunity to work in various capacities, contributing to my knowledge and expertise in Data Engineering, Power BI, and Data Visualization.