Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Remove spaces between numbers

Hi everyone,   I am clueless at the moment, I have tried all the methods available on the internet, I am starting to think it's not a space but a special character in the data set.  Here is ...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous 

    I download your pbix file and have a test. The special character may not be space, I copy value and delete number value to get it.

    I think you can use SUBSTITUTE Function to achieve your goal.

    Calculated column:

     

    NewTotal = 
VAR _col = Overalll[Total]
VAR _Result =
    SUBSTITUTE ( _col," ", "" )
RETURN
_Result

     

    Measure:

     

    M.NewTotal = 
VAR _col = MAX(Overalll[Total])
VAR _Result =
    SUBSTITUTE ( _col," ", "" )
RETURN
_Result

     

      Result:

    You can download the pbix file from this link: Remove spaces between numbers

     

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

amitchandak's avatar
amitchandak
Super User
5 years ago

Anonymous .

Try new column in M

Text.Start([Total],1) & Text.End([Total,3)

 

Try new column in Dax

left([Total],1) & right([Total,3)

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Thanks amitchandak,

 

This works short-term. I want to update the data in the future, thus there will be sometimes 2 numbers on the left instead of 1. Is there any way to overcome this problem without changing the formula every time.

Also, there might be a situation when there is just 1 number on the left for some regions and then 2 for others.