Forum Discussion

delgadomk's avatar
delgadomk
Regular Visitor
9 years ago
Solved

Error - DataFormat.Error: We couldn't convert to Number. Details J7510

Hi,   I keep receiving an error when I import my excel file into PowerBI Desktop.  I think what is happening is that in some of the fields, there is a comma. For example, I have a column titled Pr...
Need Help
  • delgadomk's avatar
    delgadomk
    9 years ago
    Thank you! I made the changes to the excel file first then made them again in the Desktop and it seems to have worked.
MarcelBeug's avatar
MarcelBeug
Icon for Community Champion rankCommunity Champion
9 years ago

There is trailing unicode 8203 character "Zero width space"

 

I investigated with the code below (of which the first part was inspired by your helpful video).

You can get rid of it by taking the first 4 characters as I posted before.

 

let
    Source = Web.Page(Web.Contents("https://es.wikipedia.org/wiki/Copa_Am%C3%A9rica")),
    Data7 = Source{7}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data7,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Column1"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Other Columns", [PromoteAllScalars=true]),
    #"Replaced Value" = Table.ReplaceValue(#"Promoted Headers","#(cr)#(lf)#(lf)Detalle","",Replacer.ReplaceText,{"Año"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Replaced Value", "Año", "Año - Copy"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Duplicated Column",{{"Año - Copy", Int64.Type}}),
    #"Inserted Text Length" = Table.AddColumn(#"Changed Type1", "Length", each Text.Length([Año]), type number),
    #"Added Custom" = Table.AddColumn(#"Inserted Text Length", "LastChar", each try Character.ToNumber(Text.At([Año],4)) otherwise null),
    #"Inserted First Characters" = Table.AddColumn(#"Added Custom", "First Characters", each Text.Start([Año], 4), type text),
    #"Changed Type2" = Table.TransformColumnTypes(#"Inserted First Characters",{{"First Characters", Int64.Type}})
in
    #"Changed Type2"
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thanks Marcel - that was helpful :smileyhappy: Faced the same problem too but had no idea about this zero width space thing. So, there's unseen data behind the numbers that's not apparent to us?