Forum Discussion
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
- Thank you! I made the changes to the excel file first then made them again in the Desktop and it seems to have worked.
22 Replies
Hi Kim,
When you are importing the data from excel to Power BI, the program automatically identifies certain columns and converts them to number. However, this step may lead to some erros, exaclty what is happening to you with your data.
If you noticed, when you import the data, in the "Applied Steps"section, the third step is "Changed Type" - this is an automatic data conversion step. To solve your problem simply remove this step and then you can convert to text whithout any errors.
I hope this helps you.
Best Regards,
João Silva.
JoaoSilva77 Your contribution is late and doesn't make sense as the data comes from a website and the objective is to convert the values to integers.
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.
- Thank you! I made the changes to the excel file first then made them again in the Desktop and it seems to have worked.
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_2305192797
Any advice please?
Hi,
I've had the same problem.
Using data with number format ( 1,233.331), and use it in a Dutch PowerBi.
The solution for me was to change the datatype from text to decimal based on country codes, in Query-editor. That is the last option in the menu when you click on the datatype symbol in the column header. Choose datatype decimal, Country English (world).
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?
Hi, Try apply a Trim & Clean from Transform Menu before the change to whole number
Regards
Victor
Lima - Peru
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!
