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 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

Need Help

6 Replies

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      drwillia
      Icon for Helper I rankHelper I
      4 years ago

      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

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

      = 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's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    4 years ago

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

    --

     

  • 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,