Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
lchirag
Frequent Visitor

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 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!

1 ACCEPTED 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

m_dekorte_0-1698865021107.png

 

I hope this is helplful

View solution in original post

3 REPLIES 3
BA_Pete
Super User
Super User

Hi @lchirag ,

 

Going to need some example data and expected outputs please.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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.

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

m_dekorte_0-1698865021107.png

 

I hope this is helplful

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors