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

[delgadomk]

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

[palash15]

I have a similar error and I am not able to get rid of it, tried all the waysy mentioned on the community. The exact error is:
DataFormat.Error: We couldn't convert to Number.
Details:
All

Please help if anyone is familiar. Thanks.

[Anonymous]

In my particular case it was ZIP code written with Latin letters

[Maaike_V]

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

[JoaoSilva77]

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.    

[MarcelBeug]

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

[Franz_Valverde]

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

Any advice please?

[Vvelarde]

@Franz_Valverde

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

Regards

Victor

Lima - Peru

[Franz_Valverde]

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]

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]

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

[bukhari1979]

1. Select that column before converting it to whole number

2. From the transform menu apply trim an clean

3. assign a default value to error occurence by using Replace Values> Replace Errors

[MAAbdullah_47]

Hi @bukhari1979  sorry your solution is misleading and not correct.

[Anonymous]

@bukhari1979 Unfortunately, when I applied these steps it replaced all the values with the default value for errors found. My Power BI goes from Data format error. We couldnt convert to number to sometimes saying Excel file is corrupt. 

Here is my Advanced Editor. Basically the Column called "Location ID" starts off as a alpha numeric value. I replace the alphas with blank or a number value then change formatting to number so i can tie the number to another table needed. Any assistance is greatly appreciated. What's odd is I im importing two excel files one loads fine the newer one is the one throwing errors. I cannot tell any difference in the Excel docs regarding naming or formats that would be the cause.

let
Source = SharePoint.Files("https://companyname.sharepoint.com/sites/talentanalytic/", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "https://companyname.sharepoint.com/sites/talentanalytic/Shared Documents/Cornerstone/Source Reports/ALL Trainings/")),
#"Invoke Custom Function2" = Table.AddColumn(#"Filtered Rows", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns2" = Table.RenameColumns(#"Invoke Custom Function2", {"Name", "Source.Name"}),
#"Removed Other Columns2" = Table.SelectColumns(#"Renamed Columns2", {"Source.Name", "Transform File"}),
#"Expanded Table Column2" = Table.ExpandTableColumn(#"Removed Other Columns2", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File (3)"))),
#"Replaced Value" = Table.ReplaceValue(#"Expanded Table Column2","AAA-","",Replacer.ReplaceText,{"Location ID"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","INS-MNSC","1111",Replacer.ReplaceText,{"Location ID"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","COM","2222",Replacer.ReplaceText,{"Location ID"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","Arizona_Remote","3333",Replacer.ReplaceText,{"Location ID"}),
#"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","Company_Company_Company","4444",Replacer.ReplaceText,{"Location ID"}),
#"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","Ontario_Remote","5555",Replacer.ReplaceText,{"Location ID"}),
#"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","Texas_Remote","6666",Replacer.ReplaceText,{"Location ID"}),
#"Replaced Value7" = Table.ReplaceValue(#"Replaced Value6","Utah_Remote","7777",Replacer.ReplaceText,{"Location ID"}),
#"Replaced Value8" = Table.ReplaceValue(#"Replaced Value7","SC_MN","8888",Replacer.ReplaceText,{"Location ID"}),
#"Trimmed Text" = Table.TransformColumns(#"Replaced Value8",{{"Location ID", Text.Trim, type text}}),
#"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Location ID", Text.Clean, type text}}),
#"Changed Type" = Table.TransformColumnTypes(#"Cleaned Text",{{"Location ID", Int64.Type}}),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", {{"Location ID", 9999}}),
#"Added Conditional Column" = Table.AddColumn(#"Replaced Errors", "Status", each if [Transcript Status] = "Completed" then "Completed" else if [Transcript Status] = "Completed (Equivalent)" then "Completed" else if [Transcript Status] = "Exempt" then "Completed" else if [Transcript Status] = "In Progress" then "In Progress" else if [Transcript Status] = "Registered" then "In Progress" else if [Transcript Status] = "In Progress / Past Due" then "Past Due" else if [Transcript Status] = "Registered / Past Due" then "Past Due" else "Unknown"),
#"Extracted Date" = Table.TransformColumns(#"Added Conditional Column",{})
in
#"Extracted Date"

[MarcelBeug]

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]

Thanks Marcel - that was helpful  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? 

[Vvelarde]

@delgadomk

Hi, In Edit Query, Change the DataType to Text

Close & Apply.

[AlanSphinx]

Thank you! It worked for me!

[Franz_Valverde]

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?

[delgadomk · Accepted solution]

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]

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.