Extract values from string using DAX

Question

I have the following live Dataset (column "Response") being generated from Microsoft Flow and written to Power BI dataset:

Response	Value
Not available	0
1500	1500
2000 Dollars	2000
coût estimatif 260.000 USD	260000
NON	0
N/A	0
Based on FY 2019/2020 Budget 70,652 USD	70652
US$220,000.00	220000

I need to extract the numbers only as shown above in the column "Value" using DAX as i cannot use Power Query. How can this be accomplished.

Regards,

Chris

greg_deckler · Answer

Chitemerere&nbsp;- Well, one way would be to use DAX to turn your strings into table variables. Then have a table that lists every possible charater you want to remove and remove any rows in that table then translate it back to a string. Although the years are going to pose problems. You can see some of the basic techniques here:&nbsp;
https://community.powerbi.com/t5/Quick-Measures-Gallery/ARABIC/m-p/1083214#M519

chitemerere · Answer

Thanks Greg for your input, most appreciated.&nbsp;Regards,Chris

mahoneypat · Answer

Another way would be to use Text.Select in the query editor. Add a custom column with a formula like this

= Text.Select([ColumnWithNumbers], {"0".."9"})

If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.

Regards,

Pat

amitchandak · Answer

Chitemerere , see if this can help
https://docs.microsoft.com/en-us/dax/isnumber-function-dax

chitemerere · Answer

Thanks Pat. &nbsp;This is a live connection to a Power BI Service Dataset so i cannot use Power Query.&nbsp;Regards,Chris

Forum Discussion

Extract values from string using DAX

7 Replies

Related content

Extract Alphanumeric Value from String

Extract part of text string

DAX: extracting string using delimiter

Extract Date from String

Extract Value