Extract Last Numeric Characters and a "-"

[beaoliv123-_]

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

[CNENFRNL]

[bolfri · Accepted solution]

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

[umanbr]

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