Power Query Filter By a Specific First Letter or Character Tutorial

This Power Query filter tutorial will demonstrate a couple of approaches to solving Excel BI’s Table Filter Challenge. The goal of this challenge is to filter out names where the 2nd word starts with the letter “C.”

This Excel BI challenge is a great opportunity to practice and hone your skills in Power BI. There are also plenty of great techniques used by experts in the field that you can learn and discover here. You can watch the full video of this tutorial at the bottom of this blog.

Power Query Filter Approach # 1

Let’s go to the Power Query editor to break this problem down. We’ll use the user interface and its tools as much as possible and look for a second word that begins with the letter C. On the Transform tab, we’ll go to Extract and select Text After Delimiter.

Power Query filter tutorial on Text After Delimiter

In the Text After Delimiter window above, we can enter a space there, press OK, and get the list below. The result below is, of course, different from what we want, but it creates a key piece of syntax for us. 

Let’s keep our starting letter at each of these strings. So in the formula bar, we’ll add another function Text.Start, keep one character, and press the check mark.

Power Query filter Text.Start function

All that’s left is to compare this result to the letter C. At the end of the equation, we can say = C and press that check mark again.

We’ve successfully created the logic we can use to filter our table. Next, we need to copy the syntax in the formula bar (Ctrl + C) and X out our step.

After that, we create a dummy filter.

Power Query filter: Dummy Filter

We paste back (Ctrl + V) the logic that we’ve just created, cut out (ctrl + x) the column reference and delete the last part. And what we get is the image below.

When you highlight that last bracket, and if it belongs to the Table.SelectRows function, then you’re good to go. Finally, instead of putting an underscore after Text.AfterDelimeter, we paste back that column selection and press that check mark.

And we see below that it works!

Power Query filter: Table.SelectRows

Power Query Filter Approach # 2

Now for our second approach in this Power Query filter tutorial, we’ll do something slightly different. We’ll use several different Power Query functions but also make the comparison case-sensitive.

Power Query filter: Approach 2

The bulk of the structure is already in our previous formula bar. So we’ll copy that syntax highlighted above and press that fx in front so we can use it to build our second approach.

We then erase that previous step reference (“Filtered Rows”) in the image above and paste back our syntax to replace it. And finally, we press that check mark again.

Split Words into a List

Instead of the Text.AfterDelimeter inside the formula bar, we’ll split up the words in a list by using Text.Split. And that includes splitting the name column and on a space just like below:

Power Query filter: Text.Sp;lit

Each word has its position in the list, and to extract the word on the second position, we can use item access. So in a set of curly brackets, we can refer to its zero-based index position. To get to something that’s at position number two, we have to enter a 1 here.

But we also want to protect this because if at a given moment, a string is entered that contains just one word at that time, there won’t be a second item in that list, and it will return an error.

Changing the required to optional item access will solve potential future problems. To do that, all we have to do is add a question mark (?) at the end.

Adding the question mark changes the required item access into optional item access. This means that if an item doesn’t exist in the list, a null is returned instead of an error.

Let’s also change the function Text.Start because we want to use a function that has an optional compare parameter. So instead of Text.Start, we’ll use Text.StartsWith, add a comma after the question mark and put “C”, the substring we’re looking for.

We can then use that Compare function, so we type in and select Comparer.OrdinalIgnoreCase. And remember to add the closing parentheses to table the select rows.

And we’re almost done here. All that’s left to do is see if this returns true, so let’s add = true at the end of the formula and press that check mark. Now the process is complete!

***** Related Links *****
How To Dynamically Merge Columns In A Power Query Table
Power Query: How To Merge Tables W/Different Columns
Extended Date Table Power Query M Function

Conclusion

In this Power Query filter tutorial, you’ve seen two approaches to a table filter challenge. The first uses the Text After Delimiter from the Transform tab alongside the Table.SelectRows and Text.Start functions.

The second, meanwhile, forgoes the use of Text After Delimiter and replaces the Text.Split function with Text.Start to split words in a list. It also makes use of the compare function Comparer.OrdinalIgnoreCase

You can explore both table filter options to find which approach suits you better!

Enterprise DNA Power BI On-Demand

The Latest

If you’ve been working with Python for data analysis or machine learning, you’ve likely come across NumPy arrays. They’re a powerful tool for handling numerical data, but sometimes, the data…

How to normalize a numpy array a quick guide.

Multiplying lists in Python is a common operation when performing mathematical computations or solving problems in data manipulation. There are multiple ways to achieve this task, depending on your requirements…

How to Multiply Lists in Python: A Simplified Guide

If you’ve been wrestling with Python lists and wondering how you can save them as a neat CSV file, you’re in the right place. One of the most common tasks…

How to Write a List to CSV in Python

Do you need to write an essay on the fall of the roman empire with accurate citations but have no time to find them? Are you tired of painfully having…

What is Caktus AI: A comprehensive overview

Pandas is a widely used Python library for data manipulation and analysis. One essential functionality that pandas can provide you is the ability to modify the structure of a dataset….

Pandas Drop Index: Efficiently Remove DataFrame Rows or Columns

Working with strings is a common task in Python. You often need to figure out whether a string contains another string. The simplest methods to check if a Python string…

7 Ways to Check if a Python String Contains Another

Ever found yourself knee-deep in Python files, struggling to import a module from a parent directory? If so, you’re in good company. Python’s import system can sometimes feel like a…

Python Import from Parent Directory: A Quick Guide

Data is the backbone of businesses these days, and having proof that you know how to handle and make the most out of data is a big deal in the…

Microsoft DP-500

In programming, you may encounter situations where a variable does not have a value. These instances are commonly represented by a special value known as null. Many programming languages, such…

Null Python: 7 Use Cases With Code Examples

Truncating a floating-point number in Python is a common operation that many programmers encounter in various applications. The process involves removing the decimal place of a float, leaving only the…

Python Truncate Flow: A Streamlined Approach for Efficient Code Execution

OpenAI’s ChatGPT tool has taken the world by storm and has been at the forefront of revolutionizing the way we generate content, do research, and even create code. And just…

Is ChatGPT worth it, let's work it out.

As you continue your journey as a Python programmer, you’ll want to write code that is more efficient, readable, and easy to maintain. The Python programming language comes with a…

Python Inline If: Simplify Your Conditional Expressions

Load More