Forum Discussion
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.
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"
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
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"
Thanks Marcel - that was helpful :smileyhappy: 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?
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
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"Hi bukhari1979 sorry your solution is misleading and not correct.
