DAX - extract numbers from text to a new number column.

[eilidh3]

Hi all,

I feel this one should be a simple fix but its just not coming to me....  

Ive tried LEFT / SWITCH functions but Ive yet to come up with the correct dax function.... I've seen this can be done very quickly within PowerQuery however the table I need to create the new column in was added as a UNION of different columns and my new table isn't showing within PowerQuery.  

I would like to show the text column below within a new column as only numbers.....  Please note Not Applicable will be equal to 0.  

Score Text	Score Number
10 Totally Satisfied	10
5 Neutral	5
1 Totally Dissatisfied	1
Not Applicable	0

Any help, as always, would be appreciated 🙂  

THANK YOU!!

[smpa01 · Accepted solution]

@eilidh3  does this work for you?

Column =
VAR _1 =
    ADDCOLUMNS ( 'Table', "new", SUBSTITUTE ( 'Table'[Score Text], " ", "|" ) )
VAR _2 =
    ADDCOLUMNS (
        GENERATE (
            _1,
            ADDCOLUMNS (
                GENERATESERIES ( 1, PATHLENGTH ( [new] ) ),
                "_txt", TRIM ( PATHITEM ( [new], [Value], TEXT ) )
            )
        ),
        "num", IFERROR ( CONVERT ( [_txt], INTEGER ), 0 )
    )
RETURN
    CALCULATE (
        MAXX (
            ADDCOLUMNS (
                'Table',
                "scoreNum", MAXX ( FILTER ( _2, EARLIER ( 'Table'[Score Text] ) = [Score Text] ), [num] )
            ),
            [scoreNum]
        )
    )

[Rohan459]

This works a charm! I would like to know how to do it the other way around, but can't seem to figure it out.