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 e...
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