Forum Discussion

drwillia's avatar
drwillia
Icon for Helper I rankHelper I
4 years ago

Extracting Alphanumeric from Dynamic String Length and Format

Hi   I have various records in PowerBI in a single text field.  I need to extract only the alphanumeric values of length 8 characters from these strings, for example H5M10499, SA900324 and 3F601561...
Need Help
YalanWu_test's avatar
YalanWu_test
Icon for Helper I rankHelper I
4 years ago

Hi, drwillia ;

You could create this funtion.

= Table.AddColumn(#"Changed Type", "Custom", each 
List.Select(Text.SplitAny([Column1], " "), each Text.Length(_)=8))

The final show:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs/PSVFwzk8sLgGSpXnJmTkKugrGBqauRkDaw9TX0MDE0hLINDQwMDCyMLY0UorViVbycFEISs0rSQQpdjFTiPBRCA4FMoMdLQ0MjI1MwGpcqyoLSzMLFDwyi1IVzE1MFYzdzAwMTc0MlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 
List.Select(Text.SplitAny([Column1], " "), each Text.Length(_)=8)),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

Best Regards,