Extracting Alphanumeric from Dynamic String Length and Format

[drwillia]

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 would be the strings id like to extract. Notice they are in different positions so i cannot tranform and extract using the right function and also the delimited varies, but the constant is the length and the fact that it is alphanumeric in nature. 

Gold Coast Council - 305E2 - H5M10499 - 100028392

HD Rental - D6 XL SU - SA900324

Ezyquip Hire 745 3F601561

Would appreciate any input on this problem,

Thanks

Daniel

[YalanWu_test]

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,

[HotChilli]

It needs the column name in brackets after Text.Length so there looks to be extra [#" characters and more extra at the end

--

[HotChilli]

You must be pretty close.  Error messages?

[drwillia]

= Table.SelectRows(#"Split Column by Delimiter", each Text.Length([#"[Fin Project].[Project].[Project].[MEMBER_CAPTION]"] = 8))

Expression.Error: We cannot convert the value false to type Text.
Details:
Value=FALSE
Type=[Type]

[HotChilli]

You can split the column by space character, using Advanced->To Rows.  That will give you a column of strings.  Then use the Filter dropdown from the column header to generate a formula (just pick one of the values, doesn't matter which, you just want to generate a Table.SelectRows statement which you can edit).

Then edit the statement to use Text.Length(column_Name) = 8 instead of the filter you generated

[drwillia]

Hi @HotChilli 

Firstly thanks for responding, ive never seen it done like this before. In the advanced editor I cant get this statement to work:

#"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter", Text.Length("[Fin Project].[Project].[Project].[MEMBER_UNIQUE_NAME]")=8

Thanks

Daniel