Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Repetitive Nonbreak space

Hello Community, Now I have a strange problem, in some columns I'm not able to change values type to decimal (1.2) : The problem is the thousands values looks like this "1 000". I thought it is a sp...
Need Help
ronrsnfld's avatar
ronrsnfld
Super User
2 years ago

It seems you did not copy exactly the data that is in your Power Query table since what you copied all had the normal <space> character.

I am assuming that the <space> characters you are unable to remove might be a NBSP (Ascii code 160).

 

That being the case, for your columns which contain numbers, you can use the following code. Instead of Table.TransformColumnTypes function, it uses the Table.TransformColumns function to remove the unwanted spaces and NBSP's, and also set the data type.

 

Given this table where some of the spaces are normal spaces and others are the NBSP.

Column1

2 555,7
123 456,78
123 456,78

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

//create a LIST of you column names containing numbers
    #"Cols of Numbers" = {"Column1"},

//create a LIST of transforms whereby
//  both NBSP and regular spaces are removed.
//  the resultant string is transformed to a number
//     using the "da-DK" culture which uses the comma for a decimal
    #"Transforms" = List.Transform(#"Cols of Numbers", (n)=> {n, each Number.From(Text.Remove(_,{" ","#(00A0)"}),"da-DK"),type number}),

//Transform the numbers and set the data types.
    #"Transform Numbers" = Table.TransformColumns(Source,#"Transforms")
in 
    #"Transform Numbers"

 

Results in the following output. Please note the output is decimal numbers with US formatting (decimal is a dot)