We couldn't convert to Number but the columns are all numbers!

[dan--dk]

Greetings, 

I have a list of 13000+ lines, the output from monitoring platform was a little dirty, some of the numbers were enclosed in quotation marks ("43.687" for example). 

Through troubleshooting this issue I have removed the quotation marks using find and replace in Excel, and also using replace value within power query. The sanitised number values appear to be the same (screenshot from notepad of one error number and one that works). There is no extra whitespace, I have tried to replace the decimal point in the whole column. 

Simply removing the quotations does not fix the issue. Has anyone seen something like this before?

For some reason, no matter what I try to do, numbers that used to be in quotes return error when converting to number.

 

Notepad check to validate no extra characters and formatting.

Original dataset in Excel including quotations.

[dan--dk · Accepted solution]

Sorry for the delay all. None of the suggestions worked. I had to go back to source data.

The issue was due to an error in conversion and some errors with numbers saved as text. 

My data had been rolling around in the mud and needed to be sanitised. 

Thank you all for your suggestions and help. 

[v-cgao-msft]

Hi @dan--dk ,

Could you please intercept this data and post example files of the conversion steps? This will help.

How to provide sample data in the Power BI Forum

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Ahmedx]

you first change the dot to a comma, and then remove the quotes

[ERD]

@dan--dk,

You can try to load data to the Power Query as it is and clean it fully from there. Here are the steps you can use:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjTSMzUzUorViVaKUTI01DM0ilEC80yN9QwNlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    
    #"Replaced Value" = Table.ReplaceValue(Source,"""","",Replacer.ReplaceText,{"Column1"}),
    #"Trimmed Text" = Table.TransformColumns(#"Replaced Value",{{"Column1", Text.Trim, type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Column1", Text.Clean, type text}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Cleaned Text",{{"Column1", type number}})
in
    #"Changed Type"

[olgad]

Have you trimmed the values after loading to make sure they dont have whitespaces?

[dan--dk]

Yes, sorry I forgot to mention that, I have also tried using the trim and clean functions on the text field, but the error persists.

It is almost like the data is corrupt, but I cannot fathom how xx.xxxx as numerical values could be different from a simliar field.