Forum Discussion

JonPT's avatar
JonPT
Regular Visitor
1 year ago
Solved

Extract Alphanumeric Value from String

Hi Power BI Desktop Forum I'm trying to extract an 8-character alphanumeric value from a column in Power BI desktop. The pattern to extract is: first 2 characters are lower case a-z, followed by an...
Need Help
  • Kaviraj11's avatar
    Kaviraj11
    1 year ago

    Hi,

     

    Create a custom column  as below

     

    = let
    Source = [String],
    Pattern = List.First(List.Select(List.Transform({0..Text.Length(Source)-8}, each Text.Middle(Source, _, 8)), each Text.Length(_) = 8 and Text.Middle(_, 0, 2) = Text.Select(Text.Middle(_, 0, 2), {"a".."z"}) and Text.Middle(_, 2, 6) = Text.Select(Text.Middle(_, 2, 6), {"0".."9"})))
    in
    if Pattern = null then null else Pattern

     

     

Kedar_Pande's avatar
Kedar_Pande
Super User
1 year ago

In the Power Query Editor, click on "Add Column" and then select "Custom Column."

if Text.ContainsAny([INPUT_COLUMN], {"a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z"}) 
then 
try 
Text.Select(Text.Middle([INPUT_COLUMN], Text.PositionOfAny([INPUT_COLUMN], {"a".."z"}, Occurrence.First), 2), {"0".."9", "a".."z"})
otherwise 
null
else 
null

If this helped, a Kudos 👍 or Solution mark would be great!
Cheers,
Kedar Pande
www.linkedin.com/in/kedar-pande