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 the file@:

https://easyupload.io/cvlp63

 

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. 

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi,

     

    I had the same problem and I found an easy solution:

     

    Select the column and click on Replace values. Then open the advanced options and select Replace using special characters -> Non-breaking Space

     

    After selecting it, it will populate the Value to Find:

     

    and just click OK.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.