Forum Discussion

stephchnva's avatar
stephchnva
Frequent Visitor
1 year ago
Solved

How do I extract the 17-alphanumeric characters in a column, delimiter

Hello guys,

 

I need help. How do I extract the 17-alphanumeric characters in the "name" column.

I've tried different ways of extracting by delimiter but it's just not working.

 

Name
24/10/2024 / 1HGCM82633A123456 / Alex Johnson 
VF1XYZ9Z9L0456789 / 06/11/2024 / Jamie Smith 
20 Dec 2023 / David 

 

Thank you!

4 Replies

    • ThxAlot's avatar
      ThxAlot
      Super User
      = Table.AddColumn(dataset, "pq", each List.Select(Text.Split([Name], " / "), each Text.Length(_) = 17 and not Text.Contains(_, " ")){0}?)

  • For simplicity and accuracy, incorporate regular express by python script,

     

     

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NYzLCsIwEEV/ZchaSGYS02ZZLCpFV4JoQxehBhJoUvGFn2+guL3nnGstI8VRcBKkgAPud5tjTVrKBkmqtS5bM/kvdHPIzzkDG1aWnbd4ufamNwdRlKo2xRKaI/5vOpeih1OKr7AUJKD1IxQsC27dJ94WgJWb7sHld/KPOEJObBh+", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t]),
        #"Added pq" = Table.AddColumn(Source, "pq", each List.Select(Text.Split([Name], " / "), each Text.Length(_) = 17 and not Text.Contains(_, " ")){0}?),
        #"Run Python script" = Python.Execute("df['py re'] = df['Name'].str.findall(r'\w{17}').apply(lambda match: match[0] if len(match)>0 else '')",[df=#"Added pq"]),
        Result = #"Run Python script"{[Name="df"]}[Value]
    in
        Result

     

     

  • Hi stephchnva 

     

    Copy the below code and paste it into the advance editor

     

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLRNzTQNzIwMlHQVzD0cHf2tTAyMzZ2NDQyNjE1A4o55qRWKHjlZ+QV5+cpKMXqRCuFuRlGREZZRln6GACVmFtYAlUZmOkbGsKM8UrMzUxVCM7NLMmA6DAyUHBJTVYAShsDpV0SyzJTgBKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t]),
        #"Added Custom" = Table.AddColumn(Source, "Custom", each List.Select(Text.Split([Name]," / "),(x)=>Text.Length(x)=17){0})
    in
        #"Added Custom"