Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
12 | |
8 | |
8 | |
7 |
User | Count |
---|---|
15 | |
13 | |
8 | |
7 | |
6 |