Error converting a number registered as text into a number

[Cado_one]

Hi,

at the first look my topic could seems similar to many others but I didn't find any solution to my problem among the dozens of topics I've read.

Let's explain, I'm beginning to use power BI to visualize some data from an API and a query in particular is problematic.

Indeed, it returns me some numeric datas in a text format. Problem is that I need this columns to have numeric type to be able to do some curves in Power view.

Here is what I tried:

- Change data type from text to Decimal number using the Data type button in power BI and in power query editor --> Gives me an error saying I can't convert to a number.

- Create a new column and convert the text numbers with VALUE(ColumnToConvert) and CONVERT(ColumnToConvert, number) --> Same error

- Change the type in the advanced editor with Table.TransformColumnTypes(#"Column1 développé",{{"Column1.value", type number}}) --> I also tried int64.Type instead of number but it doesn't work...

If someone has other ideas I'd be very glad to ear it and try.

Thanks in advance and sorry for my bad english spoken,

Cado

[Greg_Deckler · Accepted solution]

So I just had a similar issue @Cado_one and the problem turned out to be a special character. I had tried using Trim and Clean and a bunch of other steps like replacing line feeds, etc. Anyway, I found it by clicking on the Error in Power Query, copying the value, pasting into Notepad and then copying out of Notepad and into a Replace Value step in my Power Query. Here is some of the code:

    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Kudos", Text.Clean, type text}}),
    #"Removed Duplicates" = Table.Distinct(#"Cleaned Text", {"Title"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Duplicates"," ","",Replacer.ReplaceText,{"Kudos"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","#(tab)","",Replacer.ReplaceText,{"Kudos"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","#(00A0)","",Replacer.ReplaceText,{"Kudos"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","#(cr)","",Replacer.ReplaceText,{"Kudos"}),
    #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","#(lf)","",Replacer.ReplaceText,{"Kudos"}),
    #"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","#(cr)#(lf)","",Replacer.ReplaceText,{"Kudos"}),
    #"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","","",Replacer.ReplaceText,{"Kudos"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value6",{{"Kudos", Int64.Type}})

[Cado_one]

Hi @Greg_Deckler 

You were right, I did a replace and the conversion worked !

What a weird story 😌

Thanks to both of us @VSrujana @Greg_Deckler 

[Greg_Deckler]

Awesome @Cado_one ! Glad you got it, I know how frustrating that can be because I spent an hour or so trying to figure it out the other night!

[VSrujana]

Hi @Cado_one  , 

Please check if you can view the error in detail , in the query errors folder.

Usually , the possible reasons that text cannot convert to number are :

1. #N/A values in the text , 
2. Alphanumeric values

3. Any other special characters

Please check for these flags in the text columns before conversion to numbers

Thanks

[Cado_one]

Hi @VSrujana 

All the numbers in the column have a normal appearance like 96384556017.0302 and nothing more

This is a screenshot of the error appearing below the table when trying to convert

is there a location where I could have more details about the error ?

Thanks