Search code examples
powerbipowerquerym

Code to recognize a dynamic string within a string?

In my table I have a text column, and I need to check if the 5 characters of the string is specifically an exclamation point followed by 4 (dynamic) numbers - '!XXXX' - example !0422, the 4 numbers are not known, just need to know it there's a ! followed by 4 numbers.

Is there an M function to handle that without creating a 9,999 if statement?

Solution

  • add column, custom column, with formula as below, replacing [Column1] with the name of your text column

    = try if Text.Start([Column1],1)="!" and Number.From(Text.End([Column1],4))>=0 then "YES" else "NO" otherwise "NO"

    if you are not sure it will always be 5 characters, you could also check length:

    = try if Text.Start([Column1],1)="!" and Text.Length([Column1])=5 and Number.From(Text.End([Column1],4))>=0 then "YES" else "NO" otherwise "NO"