Forum Discussion

beaoliv123-_'s avatar
beaoliv123-_
Icon for Helper I rankHelper I
3 years ago
Solved

Extract Last Numeric Characters and a "-"

Hi,

 

I'd like to extract the last 19 numerical characters that appear on a string of my data. These 19 characters can start with a "-" or not. So, if the "-" exists, I want to extract it too and the last 19 numbers, if not, then i only want to extract the 19 numbers. There is also the problem which is the fact that not always the interaction_id appears at the end, but in the middle of the string:

 

An example would be:

 

Data_Inside_My_Column_4164830253820475205

This_Could_Be_The_Second_lineOfMy_Data_and_-3740692547306925483

Data_Inside_My_ColumnInTheMiddle_6387632173671847831_SoThisIsComplicated

Data_Inside_My_ColumnWithSpecialCharacter_-7283782058945492094_AndThatIsIt

 

For each one of the above lines I would like to have a column with the following:

4164830253820475205

-3740692547306925483

6387632173671847831

-7283782058945492094

 

Can you help me do that on Power BI?

 

Thanks a lot

Need Help
  • bolfri's avatar
    bolfri
    3 years ago

    Add custom column as following:

    List.First(
        List.RemoveNulls(
            List.Transform(
                Text.Split([FullString],"_"),
                each 
                    let x = try Number.FromText(_)
                    in if x[HasError] then null else _
            )
        )
    )

     

    Steps:

    1. Take FullString column and split it by _ into list

    2. For each item in the list try to convert it into number
    3. If transformations has error > give me null, else give me that value

    4. Remove all nulls

    5. Return fist item in the list

3 Replies

  • umanbr's avatar
    umanbr
    Frequent Visitor
    3 years ago

    Hi ,

    Use split column transformation in power query. Duplicate the column , split column by non-digit to digit and again by digit to non- digit will extract the numeric value.And then create a calculated column using below:

    NewColumn =
    var x=FIND("-",'Table'[text],,0)
    return
    IF(x=0,'Table'[numeric],CONCATENATE("-",'Table'[numeric]))
  • bolfri's avatar
    bolfri
    Icon for Solution Sage rankSolution Sage
    3 years ago

    Add custom column as following:

    List.First(
        List.RemoveNulls(
            List.Transform(
                Text.Split([FullString],"_"),
                each 
                    let x = try Number.FromText(_)
                    in if x[HasError] then null else _
            )
        )
    )

     

    Steps:

    1. Take FullString column and split it by _ into list

    2. For each item in the list try to convert it into number
    3. If transformations has error > give me null, else give me that value

    4. Remove all nulls

    5. Return fist item in the list