Forum Discussion
stephchnva
1 year agoFrequent Visitor
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!
Download example PBIX file with the code below
= try List.Select(Text.Split([Name], " / "), each Text.Length(_) = 17){0} otherwise nullRegards
Phil
4 Replies
- PhilipTreacySuper User
Download example PBIX file with the code below
= try List.Select(Text.Split([Name], " / "), each Text.Length(_) = 17){0} otherwise nullRegards
Phil
- ThxAlotSuper User
= Table.AddColumn(dataset, "pq", each List.Select(Text.Split([Name], " / "), each Text.Length(_) = 17 and not Text.Contains(_, " ")){0}?)
- ThxAlotSuper User
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 - Omid_MotamediseSuper User
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"