Power BI – Filtering Out Numbers in a Text Column

By: David Rohlfs

Introduction

Recently I came across a scenario where I wanted to exclude rows that had a number value in a text-based column. The goal was to do this as a Power BI Filter on the report page because this scenario was specific to this page. Oddly enough, this presented an interesting scenario that I think can be applied to many more situations in Power BI.

In this blog we will go over how to exclude numeric values from a text-based column in a Power BI Filter as well as going through some of the advantages to doing this.

When to use it

This is definitely a more niche scenario where you would have numeric values in a text column and wanted to exclude them, but data is dirty. In Power Query this wouldn’t be much of a challenge because you could just identify the rows that have numbers and get rid of them using M Code, but on the front end this gets a little difficult. The scenario that I will be talking about in this blog is when you have a column whose main rows are text based, but the occasional numeric based row is in there as well.

The exact reasons why you would ever do this are going to be very specific to your scenario. Because of this it is really hard to determine other situations where this would be important and not practical to give you a list of times where you should use this. But it is pretty cool that this can be done and is worth seeing how I did it.

Also, I have seen other solutions to this problem, but the thing is that they are very labor intensive and don’t always require the entire cell contents to be numeric values. So, I hope that this solution helps you with your situation.

Walkthrough

My goal in this walkthrough is to use a DAX calculated column on our table to determine if each row of our scenario field is entirely a numeric field, or if it also contains text. For example, we want to keep any row that has text, but filter out rows where the entire cell content is numbers.

To start, this is our sample data.

We are going to create a calculated column in our table. To do this click on the table in the fields list and select New Column in the Table Tools Ribbon.

Now we are going to write some DAX. You are free to copy the DAX that I have in the image below.

Here is the result of the new column.

Now we are going to place this column into a filter and choose to only have fields with no numbers in our visual.

Here is the result:

Conclusion

I know that this blog is a bit different because the scenario is so specific, but this same process should work for other data types that can be detected in DAX. If you want to learn more about data types or Power BI in general, I recommend reading through some of EPM Strategy’s other blogs linked below.

Links Related to This Blog:

Data Types

DAX Variables

If Statements in DAX

Slicer vs Filter

Comments