DAX: Using CONTAINSSTRING() to find strings that contains a single letter not a part of a word

Question

Let's say I have a column with product names.

Inside the column with product names, the products are given a letter to represent how well the product are selling, either A, B or C. (Ex. "B Black Charlie Shoes")

The letter doesn't necessarily appear before the product name. So sometimes the letters can appear inside or at the end of the string. (Ex. "Black Charlie Shoes B" or "Black Charlie B Shoes")

I want to be able to classify if a string contains "B" without being a part of a word somewhere inside the string, but I run into some problems.

If I want to find the strings which starts with "B" then I might write the following DAX expression:

CONTAINSSTRINGEXACT('Products'[PRODUCT_NAME],"B ")

Note the space after the letter B

This will catch all the product names that have a "B" followed by the actual names. However, this will also wrongly catch product names that ends on a "B" (Ex. "Chelsea COLLAB Shoes") since COLLAB ends on a "B".

To my understanding DAX doesn't allow for the use of regular expressions, so how do I specify that I'm only interested in letters that stands alone (not a part of a word)?

Answer 1

Something like this.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclJwyklMzlZwzkgsyslMVQjOyE8tVorVAcpgiis4Ick4oeuJBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each " " & [Column1] & " "),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Classification", each if Text.Contains([Custom], " B ") then "B" else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom"})
in
    #"Removed Columns"