Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi There!
I have a column in the PBI dataset that is TEXT and has very long text data.
Every row of this field has an Alphanumeric string added in any part of the text. However, the pattern to be extracted is always starting with ABCD0123456.
How do I search the entire text field and extract this patterned data?
Thank you in anticipation for helping me out!
Solved! Go to Solution.
Hi @lchirag
You can give this custom lookupFunction a go:
let
Source = "Purchase dept ID: PURT0001894 Software License Renewals Warehouse Area: Applications Hosting Team INITIATIVE/CATEGORY: MS Teams License renewal",
lookupFunction = ( lookIn as text, alphaCount as number, numberCount as number ) as text =>
[
len = alphaCount + numberCount,
items = List.Select( Text.Split(lookIn, " "), each Text.Length(_) = len),
find = List.Select( items, each
[
split = Splitter.SplitTextByCharacterTransition({"A".."Z"}, {"0".."9"})(_),
test = Text.Length( split{0} ) = alphaCount and Text.Length( split{1} ) = numberCount
][test]
),
combi = Text.Combine( find, ", ")
][combi],
result = lookupFunction(Source, 4, 7)
in
result
or maybe this
let
Source = "Purchase dept ID: PURT0001894 Software License Renewals Warehouse Area: Applications Hosting Team INITIATIVE/CATEGORY: MS Teams License renewal",
lookupFunction = (lookIn as text, alphaCount as number, numberCount as number) as text =>
let
patternLen = alphaCount + numberCount,
isPatternMatch = (text) =>
( Text.Remove(Text.Start(text, alphaCount), {"A".."Z"}) = "" and
Text.Remove(Text.End(text, numberCount), {"0".."9"}) = "" ),
items = List.Select(Text.Split(lookIn, " "), each Text.Length(_) = patternLen),
matches = List.Select(items, each isPatternMatch(_))
in
Text.Combine(matches, ", "),
Result = lookupFunction(Source, 4, 7)
in
Result
both return this result
I hope this is helplful
Hi @lchirag ,
Going to need some example data and expected outputs please.
Pete
Proud to be a Datanaut!
Hi @lchirag
You can give this custom lookupFunction a go:
let
Source = "Purchase dept ID: PURT0001894 Software License Renewals Warehouse Area: Applications Hosting Team INITIATIVE/CATEGORY: MS Teams License renewal",
lookupFunction = ( lookIn as text, alphaCount as number, numberCount as number ) as text =>
[
len = alphaCount + numberCount,
items = List.Select( Text.Split(lookIn, " "), each Text.Length(_) = len),
find = List.Select( items, each
[
split = Splitter.SplitTextByCharacterTransition({"A".."Z"}, {"0".."9"})(_),
test = Text.Length( split{0} ) = alphaCount and Text.Length( split{1} ) = numberCount
][test]
),
combi = Text.Combine( find, ", ")
][combi],
result = lookupFunction(Source, 4, 7)
in
result
or maybe this
let
Source = "Purchase dept ID: PURT0001894 Software License Renewals Warehouse Area: Applications Hosting Team INITIATIVE/CATEGORY: MS Teams License renewal",
lookupFunction = (lookIn as text, alphaCount as number, numberCount as number) as text =>
let
patternLen = alphaCount + numberCount,
isPatternMatch = (text) =>
( Text.Remove(Text.Start(text, alphaCount), {"A".."Z"}) = "" and
Text.Remove(Text.End(text, numberCount), {"0".."9"}) = "" ),
items = List.Select(Text.Split(lookIn, " "), each Text.Length(_) = patternLen),
matches = List.Select(items, each isPatternMatch(_))
in
Text.Combine(matches, ", "),
Result = lookupFunction(Source, 4, 7)
in
Result
both return this result
I hope this is helplful