Forum Discussion
Removing a strange space between numbers
Hi,
there is a strange space between my data which I have no clue how to remove:
I have tried several methods, yet none of them worked.
It causes that my data is defined as text, even though it's clearly numbers.
Here is the file for those who would like to get their head around it:
EDIT: LEFT and RIGHT dax funcitons is not an option as data is dynamic, hence the numbers will be left 2 for some numbers and left 1 for the others.
Have you tried clean in power query? Transform menu.
if that doesn't work, click one of the cells in Power Query so you see the text at the bottom of the screen. Copy the space character to the clipboard, then transform, replace values and paste the character.
3 Replies
- MattAllington
Community Champion
Have you tried clean in power query? Transform menu.
if that doesn't work, click one of the cells in Power Query so you see the text at the bottom of the screen. Copy the space character to the clipboard, then transform, replace values and paste the character.
Many thanks MattAllington,
your solution worked splendidly.
Heads-up: remember to "replace using special characters"
Special space character between groups of numbers is #(00A0)
The code to remove those spaces and have clean numbers castable to int64 is:
Table.ReplaceValue(#"Previous step","#(00A0)","",Replacer.ReplaceText,{"Column 1"})
