Error converting a text column (that contains only numbers) to number type

[valcat27]

Hello all, 

I am importing a csv file with 5 columns and one of them has only decimal numbers between 0 and 1 but power BI import it as text type.  I need it as number type, but when  try to convert it to number type, I get the error: DataFormat.Error: We couldn't convert to Number.

I thought it was due the first line that contains the headers, but even after I select "Use First Row As Headers" and try to convert it to number type, I get the error.  

This is how the column looks like with the first line as headers:

I also tried to change the column name to a number, before importing, or round the values to two decimal places, but it does not help.

Can anyone help me importing/converting it to number type?

Thanks in advanced.

[valcat27 · Accepted solution]

I did not find the problem but I tried to save my file in xlsx format and import it to power BI and it works, the column is recognized as number type. 

[valcat27]

Hello @Greg_Deckler  and @mahoneypat ,

Thank you for your answers. 

I tried the clean and trim operation and then to convert to decimal number, but I got the same error. 

Also, I do not have blanks/nulls values and the number of rows can vary a lot because I need to import many files with this structure. I can have less than 100 rows or more than 500 rows, but I cannot tell you the maximum that I will have. 

[Anonymous]

the leading and trailing spaces doesn't disturb usually.

perhaps could be some problem with you local enveronment.

try this and let we know what appens:

 Table.SplitColumn(Origine, "Colonna1", Splitter.SplitTextByCharacterTransition({"0".."9","."}, (c) => not List.Contains({"0".."9","."}, c)), {"Colonna1.1", "Colonna1.2"})

anhoter last think: try this fo onle the first few rows: you could have some rows at the bottom of table wich idoes'nt look like as you aspect.

[valcat27]

Hello @Anonymous ,

Thank you for your answer. 

Can you explain me what this code is for?

I copied it to my advanced editor substituting "Origine" by the last step name there and "Colonna1" by "CS". Is it correct? 

This was the result:

After that I try to convert "Colonna1.1", but I got the error. 

[mahoneypat]

Try to remove non-printing characters by doing a Trim on that column (on transform tab under Format).

Pat

[Greg_Deckler]

@valcat27 Try doing a clean and a trim operation on it to remove any odd characters or trailing spaces. Also try filtering out blanks/nulls. How many rows are there total?