Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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
User | Count |
---|---|
23 | |
18 | |
16 | |
13 | |
11 |