cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

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

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

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

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.    

Super User
Super User

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

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

 

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

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

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

 

 

Mirza Frequent Visitor
Frequent Visitor

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

Thanks that worked for me. In advanded editor when selecting Changed Type and finding the field NAMEID it showed 

... {"NAMEID", Int64.Type}, ....

 

Then I just changed it to

... {"NAMEID", type text}, ....

 

and it worked Smiley Happy

 

 

 

 

Highlighted
bukhari1979 Frequent Visitor
Frequent Visitor

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

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

 

crptsl Frequent Visitor
Frequent Visitor

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

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