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.
Franz_Valverde's avatar
Franz_Valverde
Frequent Visitor
9 years ago

Hello!

 

I have the same error when importing web data from: https://es.wikipedia.org/wiki/Copa_Am%C3%A9rica

Trying to convert the year that is a string data type to the integer data type.

 

This error is only displayed in some cases (rows).

I copied the column Year (Year), to Year-Copy (Year-Copy) and aplyed Whole Number Conversion.

As taught in the Microsoft Virtual Academy course: https://mva.microsoft.com/en-US/training-courses/initiating-with-power-bi-16911?l=Zj9Ql9MED_2305192797

 

Any advice please?

Vvelarde's avatar
Vvelarde
Community 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
    8 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
      Community Champion
      8 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
        8 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