Find a text with and without space

[prajaktakeni11]

Hello ,
I want to filter text column which includes "tag within sale" , So I have used Search fucntion for it 
however my data is not structured so it includes "tag   within  sale " (Data with space as well)
how can I filter both (with and without space )values 
Please help ,and Thank you in advance 

[v-xuxinyi-msft · Accepted solution]

Hi @prajaktakeni11 

Here is my testing:

My sample:

I created a calculated column as follows.

Column 2 = 
VAR _space = IF(CONTAINSSTRING('Table'[column], " "), 1, 0)
RETURN
IF(_space <> 0, "within space", "without space")

And then put this column into the slicer.

Is this the result you expect?

Best Regards,
Community Support Team _Yuliax

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[prajaktakeni11]

@v-xuxinyi-msft Thank you for your reply , but I am not looing for this soultion I want both (with and without space should be considered as one value)

[123abc]

To filter text values in Power BI that include variations of "tag within sale" with and without spaces, you can use a combination of Power Query functions. Assuming you have a column named "TextColumn" that contains the text data, you can follow these steps:

1. Open Power BI and go to the "Home" tab.

2. Click on "Transform Data" to open the Power Query Editor.

3. In the Power Query Editor, find and select the column containing your text data (e.g., "TextColumn").

4. Click on the "Transform" tab and then select "Replace Values" from the dropdown menu.

5. In the "Replace Values" window:

   • In the "Value to Find" box, enter "tag within sale" (with spaces).
   • In the "Replace with" box, enter a unique string that doesn't occur naturally in your data, such as "###tagwithinsale###".

6. Click "OK" to close the window.

7. Now, repeat steps 4-6, but this time replace "tagwithin sale" (without spaces) with the same unique string "###tagwithinsale###".

8. Click on the "Home" tab again and select "Close & Apply" to apply the changes and close the Power Query Editor.

9. In your report, use the filter option to filter the "TextColumn" based on the unique string "###tagwithinsale###".

By doing this, you've replaced both variations of "tag within sale" with a common string. Now, you can easily filter the column based on this unique string, and it will capture both versions of the text, with and without spaces.

Remember that this approach assumes that the unique string you chose ("###tagwithinsale###") does not naturally appear in your data. Adjust the string as needed based on your specific data characteristics.

[prajaktakeni11]

@123abc  thank you for your reply ,In my case data is keeps on changing it is not static (space between the text is not spesific it keep on changing )

[123abc]

If the spacing between the words in your text data is inconsistent and keeps changing, and you want to capture variations in spacing, you can use a more flexible approach using regular expressions. Regular expressions allow you to define patterns that match different spacing variations. Here's how you can do it:

Using Custom Column with Regular Expression in Power BI:

1. Create a Custom Column:

   • In the Power BI Desktop, go to the "Home" tab.
   • Click on "Transform data" to open the Power Query Editor.

2. Add a Custom Column:

   • In the Power Query Editor, click on "Add Column" in the ribbon.

   • Choose "Custom Column."

   • In the formula bar, enter the following expression:

CustomColumn = if Text.ContainsRegex([YourColumnName], "(?i)tag\s*within\s*sale") then "Yes" else "No"

1. Replace [YourColumnName] with the actual name of your text column.

   • Click "OK" to create the custom column.

   The regular expression (?:tag\s*within\s*sale) will match variations like "tag within sale," "tag within sale," "tagwithin sale," and any other combinations where there might be spaces between the words.

2. Filter based on Custom Column:

   • Go back to the main Power BI window.
   • You will now see the new custom column. Use this column to filter your data.

This approach allows for flexibility in handling variations in spacing between the words in your text data. Adjust the column name and conditions as needed based on your actual column names and data.