How to Avoid Case Sensitivity Trap in Power BI Conditional Columns
Nesting IF statements are fairly standard in Excel, but you can also use them in Power BI. There is an issue if you enter 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 I used them in Power Query (Option 2 below).
Note: I am showing Option 1 first, using 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 based on the 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 contained the word “Product,” the Conditional Columns would be evaluated 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, which is the only value in the column.
Original Data
Original Data
Option 1: Nested If Statements
The Power BI Calculated Column formula below 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.
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 simultaneously.
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.
Open the Query Editor.
Select the Query you wish to edit.
Select Conditional Column from the Add Column menu bar, which brings up the entry tab.
Fill out the rule that matches your conditions.
Add Rules until all your conditions are met.
Remember to put in a value for Otherwise to capture any values that do not meet 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 it does not work where cases are different.
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 can see below, the table has all the values correct. 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 typical case.
As with Option 1, if you use next IFs, as in the calculated column in Power BI as a calculated measure, they are not case-sensitive.
Final Working Version
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. 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.
