Forum Discussion
Solved
Extract Alphanumeric Pattern from Text Field
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 e...
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 resultor 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 Resultboth return this result
I hope this is helplful
Purchase dept ID: PURT0001894 Software License Renewals Warehouse Area: Applications Hosting Team INITIATIVE/CATEGORY: MS Teams License renewal This is an example of what kind of text field I have, need code to search for "PURT0001894" it can be either in the beginning, middle or end of the text field. But it can only be once. Please help.
m_dekorte
Resident Rockstar
Resident RockstarHi 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