Have you ever tried using Microsoft Power BI to look through data using certain characters and found the filter function doesn’t return the results you were expecting? Let’s take a quick look at why and how to solve this.
I had a customer recently contact me about searching through a customer name field to find all their customers that have an asterisk in the name. However, when they typed an asterisk in the filter box, it still returned all results. This is because an asterisk is a special character known as a wildcard character. Wildcard characters are used to do more advanced filtering. There are two pretty common wildcards that can be used for filtering.
The first one we will look at is the ? symbol. The ? symbol represents a single unknown character. So if we are looking for the companies “Hobby Lobby”and “Hibbett Sports”, we would change the Filter type to “Advanced filtering”.We would then make sure to select “contains” and type “H?BB” in the filter field as shown in the sample below.
This example will look for any customer that contains four consecutive characters where the first character is an “H” and the last two characters are “BB”. This filter will pick up “Hobb” and “Hibb” to find both of our customers.
The second wildcard is an * (asterisk). An asterisk is used to represent multiple (or no) unknown characters, unlike the question mark which represents a single unknown character. Using the filter “M*A” would return results with words such as META or MAP. Both of these results have an “M” that is followed with an “A” later in the string.
Wildcards are great for advanced searching, but at times they can also cause issues. What happens when we want to search for a value for a character that is considered a wildcard character such as an asterisk?
In comes a tilde (~). While this poor symbol is probably one of the least used keys on the keyboard for most users, it is an important symbol in Power BI filtering. The tilde is used as what we refer to as an "escape character". An escape character tells Power BI to look for the literal character after the tilde.
Using this knowledge, if I am looking for a list of customers with an asterisk in the name, I would type “~*” in the search filter as shown in the image below.
Now Power BI will return all results that contain an asterisk in the column we are searching.
Competitive Edge
Please contact us to explore how SDP can leverage Power BI to create a competitive edge for your business.
About the author
Kevin Williams is a Senior Software Engineer and Power BI Practice Lead at Software Design Partners. He has a Microsoft Certified Solutions Associate: Business Intelligence Design certificate and focuses in business intelligence and .NET development.
