Forum Discussion
Search for a specific text pattern
Hi, I'm wanting to search for a particular pattern in a column (3 digits then 4 digits seperated by a dash) and return true / false if there's a match
for example: 123-4567 True A23-4567 False 000-8900 True
|
|
Andybr maybe try something like this, add a new column:
Both Numbers = VAR __left = IF ( IFERROR ( VALUE ( LEFT ( Match[Data], 3 ) ), -1 ) = -1, FALSE(), TRUE() ) VAR __right = IF ( IFERROR ( VALUE ( RIGHT ( Match[Data], 4 ) ), -1 ) = -1, FALSE(), TRUE() ) RETURN IF ( __left && __right, TRUE(), FALSE() )Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
2 Replies
Andybr maybe try something like this, add a new column:
Both Numbers = VAR __left = IF ( IFERROR ( VALUE ( LEFT ( Match[Data], 3 ) ), -1 ) = -1, FALSE(), TRUE() ) VAR __right = IF ( IFERROR ( VALUE ( RIGHT ( Match[Data], 4 ) ), -1 ) = -1, FALSE(), TRUE() ) RETURN IF ( __left && __right, TRUE(), FALSE() )Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Intrinsically, DAX isn't designed for the purpose of pattern check,
M might do the trick, but fairly verbose,
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQy1jUxNTNXitWJVnJE5hgYGOhaWBoYgDmWlrpmQABm6wIljIFAKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]), #"Added Custom" = Table.AddColumn( Source, "Chk_PQ", each let l = Text.Split([Data], "-"), chk1 = List.Count(l) = 2, chk2 = (Text.Length(l{0}) = 3) and List.Accumulate(Text.ToList(l{0}), true, (s,c) => s and not (try Number.From(c))[HasError]), chk3 = Text.Length(l{1}) = 4 and List.Accumulate(Text.ToList(l{1}), true, (s,c) => s and not (try Number.From(c))[HasError]) in chk1 and chk2 and chk3 ) in #"Added Custom"Of coz, nothing is match for Regular Expression in this regard,
RegEx Check
