Forum Discussion
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 any 6 numbers and can be anywhere in the column.
The extracted 8 character alphanumeric should then be added to a new column
If the input column contains no 8-character (2 a-z and 6 numbers) alphanumeric then output null to the output column.
INPUT_COLUMN
ab123456
zy654321
fg456123
some-text-ab123456
some-zy654321-text
fg456123-some-text
some-text
some-other-text
OUTPUT_COLUMN
ab123456
zy654321
fg456123
ab123456
zy654321
fg456123
null
null
Tried all combinations of Text.Range, Text.PositionOf, Text.Select, Text.Middle, Text.Length, Text.Remove with no success
Really hoping you can help me here.
Thanks in advance
Regards
JonPT
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
4 Replies
JonPT See if this similar solution helps: Solved: Remove first characters that are numbers - Microsoft Fabric Community
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 PatternIn 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
nullIf this helped, a Kudos 👍 or Solution mark would be great!
Cheers,
Kedar Pande
www.linkedin.com/in/kedar-pande
