Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
eilidh3
Frequent Visitor

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

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 TextScore Number
10 Totally Satisfied10
5 Neutral5
1 Totally Dissatisfied 1
Not Applicable 0

 

Any help, as always, would be appreciated 🙂  

 

THANK YOU!!

1 ACCEPTED SOLUTION
smpa01
Super User
Super User

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

 

smpa01_0-1638462340233.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

2 REPLIES 2
smpa01
Super User
Super User

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

 

smpa01_0-1638462340233.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors