Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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
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.
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?
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.
User | Count |
---|---|
123 | |
69 | |
67 | |
58 | |
52 |
User | Count |
---|---|
183 | |
92 | |
67 | |
62 | |
53 |