SQL PATINDEX equivalent in DAX

[smpa01]

Hello experts,

Does DAX have anything equivalent to SQL's PATINEDX?

For example, my datasource is following

description

Transaction 12345678-1

Tx234567890-2

45678901-12

55667788-20

Inv# 12457800-2

Can we tell DAX to look into the string and whenever it finds a pattern as following to do necessary extraction

[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]-[0-9]

or

[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]-[0-9][0-9]

Thank you in advance.

[Greg_Deckler · Accepted solution]

Nothing in DAX that I am aware of that does anything like this. @ImkeF , anything in Power Query?

[ImkeF]

Hi @smpa01 

there's no such function in M. (nor is there Regex).

But for your pattern, the custom function would look like so:

(SearchText as text, PatIndexString as text) =>

let
   // PatIndexString = "[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]-[0-9]",
   // description = "45678901-12", 
    description = SearchText,
    SplitByBrackets = Text.SplitAny(PatIndexString, "]["),
    CleanUpList = List.Select(SplitByBrackets, each _ <> ""),
    PrepareList = List.Transform(CleanUpList, each if Text.Length(_) > 1 and Text.Contains(_, "-") then "{" & Text.Replace(_, "-", "..") & "}" else _),
    PatIndexStringList = List.Buffer(List.Transform(PrepareList, each try List.Transform(Expression.Evaluate(_), Text.From) otherwise _)),

        LengthPatIndexString = List.Count(PatIndexStringList),

    TextToList = List.Buffer(Text.ToList(description)),
    LG = 
        List.Generate( () => 
        [ Counter = 0, PatIndexCounter = 0],
        each [Counter] <= List.Count(TextToList) and (try [PatIndexCounter] <= List.Count(PatIndexStringList) otherwise false) ,
        each [Contains = 
                if Type.Is(Value.Type(PatIndexStringList{[PatIndexCounter]}), type list) 
                    then  List.AnyTrue(List.Transform(PatIndexStringList{[PatIndexCounter]}, (x) => Text.Contains(TextToList{[Counter]}, x))) 
                    else Text.Contains(TextToList{[Counter]}, PatIndexStringList{[PatIndexCounter]}) 
                         
                ,Counter =  [Counter] + 1
                ,PatIndexCounter = if Contains then [PatIndexCounter] + 1 else 0
 
            ]),

    Custom1 = List.Last(LG),
    Custom3 = if Custom1[PatIndexCounter] = LengthPatIndexString then Text.Range(description, Custom1[Counter] - LengthPatIndexString, Text.Length(description) - ( Custom1[Counter] - LengthPatIndexString) ) else ""
in
    Custom3

It searches for the pattern and grabs the string that matches the pattern in modus "%..%". So the first match found anywhere will be returned.

[smpa01]

@Greg_Deckler  thanks for the confirmation.

@ImkeF  thanks for taking time out to devise a native M solution.

In my current situation I query SQL tables and want to finish most of data transformation on SQL side as despite M being so awesome it has a performance issue. Now my SQL table has 4M+ rows and if I devise a M solution it might affect the performance. That is what I am most scared about. Before importing my tables from SQL to PBI I forgot to do this transformation and I was hoping DAX has a solution to this as despite being unnecessarily complex DAX is blazing fast. Also, Marco points out the readers here https://www.sqlbi.com/articles/comparing-dax-calculated-columns-with-power-query-computed-columns/ to do transformation in DAX for the sake of performance.

However, I appreciate you taking time out of your schedule and going extraordinary lengths to help me out. And, I am going to add a R powered  M solution here to the problem as well for the sake of documenting multiple solutions to the same problem. And this query result renders in the PBI service as well.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCilKzCtOTC7JzM9TMDQyNjE1M7fQNVSK1QFKVUD5lga6RmARKM9Q1xDCNzU1MzM3t7DQNTJQAAKwmGdemTLQJBNTcwsDsL5YAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Description = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Description", type text}}),
    #"Run R script" = R.Execute("# 'dataset' holds the input data for this script#(lf)x<-dataset#(lf)x#(lf)library(stringr)#(lf)numextract <- function(string){str_extract(string, ""\\d{8}[-]\\d*"")}#(lf)x$result<-numextract(x$Description)",[dataset=#"Changed Type"]),
    #"""x""" = #"Run R script"{[Name="x"]}[Value]
in
    #"""x"""

[ImkeF]

Hi @smpa01 ,

no problem, this method will make a good blogpost 😉

If your source in SQL, then the fastest version is probably to use a SQL-script in the query editor: https://support.office.com/en-us/article/import-data-from-database-using-native-database-query-power...