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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
HAL
Regular Visitor

Separate Text from Numbers in a mixed column

Hello Community:

In my data source (csv or Excel), there is a column that contains text, numbers and blanks. I need to do calculations on a subset of the numbers, so I need them in numbers format. I tried the "value" function, but as designed, it gives me an error, because it cannot convert a text to a number. So before I apply that I need to separate the text form the numbers. I tried a couple of things but so far unsuccessfully.

 

I am glad for a hint.

 

Thanks,

Hal

4 REPLIES 4
Greg_Deckler
Super User
Super User

Can you post some sample data? There are LEFT and RIGHT and FIND functions in DAX similar to Excel and as @MattAllington pointed out, there are a lot of text parsing functions in "M" as well.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Here is some sample data from the column:

84.000.000
84.000.000
84.000.000
***
***
184.000.000
***
74.200.000
184.000.000
74.200.000
***
***
***
***
184.000.000
ASY
ASY
ASY

In fact, I have a couple of thousand records. Currently the column ist "text" format", but I need to have access to the numeric values in the column and create average, min, max etc. from that. So I thought I create a new column with just the numeric values and format the new coklumn as "number". But I cannot figure out, how to extract the new column.

Ok. This is good. You could simply convert the data type of the column to numeric, then "remove errors", and then filter out null values. That would leave you with a column of numbers. Does that work?



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.
MattAllington
Community Champion
Community Champion

When you load the data (get data - which is actually power query), you can select your column and split it into 2 based on the space. Of course that may not be what you need, but the exact transformation will depend on the exact shape of the data.  



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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