Skip to content
5MinuteBI Creating Data Power Users 5 Minutes at a time

5MinuteBI

Creating Data Power Users, 5 Minutes at a Time

  • Home
  • Blog
  • About
  • Contact
Home / Data Visualization / Power BI Data Visualizations /

How to Avoid Case Sensitivity Trap in Power BI Conditional Columns

BySteve Young Updated onMay 14, 2023

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.

Contents
  • The Situation
  • Option 1: Nested If Statements
    • Lesson Learned
  • Option 2: Using the Query Editor and Conditional Columns for Groupings
  • Wrap-up

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.

Related

Steve Young

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.

Facebook Twitter Instagram YouTube Linkedin Pinterest

Post navigation

Previous Previous
How To Automate SQL Azure Stored Procedures Using Runbooks and Email
NextContinue
How Steve Jobs, Apple Engineers and $60 Reignited My Passion
Search

Categories

  • Business Intelligence (0)
    • Self-Service BI (3)
  • Data Analytics (0)
    • Azure Data & Analytics (6)
    • Data Analysis With Python (3)
    • Data Storytelling (2)
  • Data Architecture (0)
    • Data Governance (5)
    • Data Modeling (2)
    • Power BI Solution Architecture (11)
  • Data Visualization (0)
    • Power BI Data Visualization Framework (2)
    • Power BI Data Visualizations (7)
  • Technical Education and Training (0)
    • AI in Education Content Creation (1)
    • Creating Training Materials (3)
    • Learning Paths in Tech (2)
    • Visual Learning (2)

Meta

  • Log in
  • Entries feed
  • Comments feed
  • Powered by WordPress.com.

Disclaimer: The views expressed are my own. I offer no guarantees for the accuracy of the information shared, and is for educational purposes only.

All non-original photography is sourced and licensed from my account on PEXELS,  STORYBLOCKS, iStockPhoto, and Pixabay. Please use our Contact Page if you have a question.

The information provided on this blog is for educational purposes only. Steve Young is not responsible for any errors or omissions or for any actions taken based on the information provided on this blog.

© 2023 5MinuteBI

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish.Accept Read More
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT
  • Home
  • Blog
  • About
  • Contact