Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
Nothing in DAX that I am aware of that does anything like this. @ImkeF , anything in Power Query?
Nothing in DAX that I am aware of that does anything like this. @ImkeF , anything in Power Query?
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@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"""
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...
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |