cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
delgadomk Frequent Visitor
Frequent Visitor

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 Procedure Code and typically Procedure codes are just all numbers like 99213 but there are occassions when a Procedure Code will contain a modifier and that modifier is identified after theProcedure Code and is separated with a comma: 99213,PRO or 99213,52,AS.  How can I get PowerBI Desktop to reverse the error and just import the data like it is in the excel file?

 

Thanks,

Kim

1 ACCEPTED SOLUTION

Accepted Solutions
delgadomk Frequent Visitor
Frequent Visitor

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

Thank you! I made the changes to the excel file first then made them again in the Desktop and it seems to have worked.
16 REPLIES 16
Super User
Super User

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

@delgadomk

 

Hi, In Edit Query, Change the DataType to Text

 

ChangeType.png

 

ChangeType2.png

 

Close & Apply.




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Super User
Super User

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

Typically, a step "Changed Type" is created when importing data from Excel.

You can adjust the generated code so your column will be changed to type text.

Specializing in Power Query Formula Language (M)
delgadomk Frequent Visitor
Frequent Visitor

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

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 Frequent Visitor
Frequent Visitor

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

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 Whole Number data type.

 

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

 

I copied the column Año (Year), to Año-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_23051927...

Any advice please?

Franz_Valverde Frequent Visitor
Frequent Visitor

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

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).

Screen01.png

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_23051927...

 

Any advice please?

Super User
Super User

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

@Franz_Valverde

 

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

 

Regards

 

Victor

Lima - Peru




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Franz_Valverde Frequent Visitor
Frequent Visitor

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

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!

Super User
Super User

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

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"
Specializing in Power Query Formula Language (M)
Franz_Valverde Frequent Visitor
Frequent Visitor

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

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