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