Repetitive Nonbreak space

[Anonymous]

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 space, so I tried to trim the column, it didn't work, then I tried to replace values (Replace space with nothing), it didn't work as well. The solution I found till now is to copy one cell (like this :  2 555,7   the delete 2 and 5s and 7 to keep that value, and replace it with nothing. Now this solution is not really appreciated cuz I have a lot of queries with a lot of columns with the same issue.

Thank you.

[ronrsnfld]

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)

[spinfuzer]

Maybe try removing anything that isn't a "-", "," or number?  Replace Column1, Column2, and Column3 with your column names.

let
    priorStep = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    clean = 
            Table.TransformColumns(
            priorStep,
            List.Accumulate(
                {"Column1","Column2","Column3"},
                {},
                (state,current) => state & {{current, each Number.From(Text.Combine(List.Select(List.Buffer(Text.ToList(_)), (T) => try Value.Is(Number.From(T), type number) otherwise T = "-" or T = ","))), type number }}
            )
            )
in
    clean

[j_ocean]

Are you applying the replace function / trim / clean *before* it's converted to decimal? 

[Anonymous]

Yes, and nothing happens even visually till I copy one value and delete the number and the coma to keep the nonbreak space, at this moment it works, but I want a general approach to apply it to the whole model, not column by column cuz they are numerous.

[j_ocean]

Wierd. But if I'm undertanding you right you have code that works you just want to avoid the clicks to apply it repeatedly? You can use the unpivot other columns function to put the values in one column, apply your fix, then pivot them back (or skip the last pivot depending how you want to structure your data model).

[Anonymous]

Have you tried applying Text.Clean to your column?

--Nate

[Anonymous]

Hi watkincc and thank you for your response,
I tried without any result, in addition, I'm looking for a general solution cuz as mentioned the issue presented in many queries and many columns.