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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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. 

AndrejBI_0-1602757575283.png

Here is the file@:

https://easyupload.io/cvlp63

 

1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
Community Support

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:

1.png

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. 

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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

svizsi_0-1647943492677.png

 

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

svizsi_1-1647943582569.png

 

and just click OK.

 

v-rzhou-msft
Community Support
Community Support

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:

1.png

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
Super User
Super User

@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
Not applicable

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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