Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
68 | |
61 | |
18 | |
17 | |
13 |