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.
Vvelarde's avatar
Vvelarde
Icon for Community Champion rankCommunity Champion
9 years ago

Franz_Valverde

 

Hi, Try apply a Trim & Clean from Transform Menu before the change to whole number

 

Regards

 

Victor

Lima - Peru

Franz_Valverde's avatar
Franz_Valverde
Frequent Visitor
9 years ago

Hi Victor!

Thanks for you help!

I did it such you suggested it;

I trimmed and cleaned from format option in transform ribbon menu.

 

But I got de same result, transformation error:

 

DataFormat.Error:

We couldn't convert to Number. Details: 1916​

 

Regards!

  • MarcelBeug's avatar
    MarcelBeug
    Icon for Community Champion rankCommunity Champion
    9 years ago

    Unfortunately you didn't mention which table from the website you are using, so I just picked one.

     

    This works fine if you take the first 4 characters before converting to numbers.

     

    let
    Source = Web.Page(Web.Contents("https://es.wikipedia.org/wiki/Copa_Am%C3%A9rica")),
    Data13 = Source{13}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data13,{{"Jugador", type text}, {"Selección", type text}, {"Año", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Año", "Año - Copy"),
    #"Extracted First Characters" = Table.TransformColumns(#"Duplicated Column", {{"Año - Copy", each Text.Start(_, 4), type text}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Extracted First Characters",{{"Año - Copy", Int64.Type}})
in
    #"Changed Type1"
    • Franz_Valverde's avatar
      Franz_Valverde
      Frequent Visitor
      9 years ago

      Hi Marcel!

      Thanks for your help!

       

       

      Are not you able to see the image I posted?

      It's Torneos Table!

       

      I made a video in order to be clear enough.

      Please see it at https://www.youtube.com/watch?v=REdTfgQMNoY

       

      Thanks in advance!

      Franz

      • 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"