Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |