Forum Discussion

lchirag's avatar
lchirag
Frequent Visitor
2 years ago
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 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!

Need Help
  • m_dekorte's avatar
    m_dekorte
    2 years ago

    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

3 Replies

    • lchirag's avatar
      lchirag
      Frequent Visitor
      2 years ago
      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's avatar
        m_dekorte
        Resident Rockstar
        2 years ago

        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