[DataFormat.Error] We couldn't convert to Number.

[AnnaFenton]

The error "[DataFormat.Error] We couldn't convert to Number." occurs. I have double checked the list and made sure these are all numbers. There is no error, text or empty rows in this column. I dont understand why it shows we couldn't convert to Number. Could anyone help me with this error? 

[IanSwanepoel]

Hi Anna,

Are you getting your data from an excel file? If so it's quite possible that there are either spaces in the field before or after the numbers which can cause this, in which case you'd have to go into query editor and trim the column before converting it.

There can also be false blank values, my excell often gives me strange blanks like these where they are recognised as a value instead of blank,

As seen above, the values in the column are not null, but they aren't spaces either so trim does not work.


In this case replacing nothing with null before conversion generally works for me

Kind Regards,
Ian

[AnnaFenton]

Hi Ian, 

Thank you for sharing the tip. I will keep this in mind. 

I did get the data from CSV excel file. 

I would thought "null" would always appear on the top when I apply filter on the column or maybe not... I converted null with nothing and tried the trim as you suggested.  I then converted the column into whole number. Finally I clicked "Apply". Same error still occurs.

Although I later downloaded the file with no error from my course instructor and could continue working on it, I'm still curious to know why this error still occurs.

Thank you very much for your time.

Kind regards,

Anna

[Ashish_Mathur]

Hi,

Share the link from where i can download your file.

[AnnaFenton]

Hi Ashish, 

Apologies. The data souce is an excel CSV file not from website link 😞 

Kind regards,

Anna 

[v-chuncz-msft]

@AnnaFenton,

Download the latest version of Power BI Desktop and share us the code in The Advanced Editor.

[AnnaFenton]

Hi v-chuncz-msft,

Thank you for your reply.  I pasted the code in Advanced Editor below.  As you can see, I have trimmed the Country-ID column and converted it into whole number. The error still occurs when I click apply. Many thanks for your time in advance. 

Kind regards,

Anna

let
Source = Table.Combine({#"Population-country-1950-1999", #"Population-country-2000-2049", #"Population-country-2050-2100"}),
#"Removed Columns" = Table.RemoveColumns(Source,{"AgeGrpStart", "AgeGrpSpan"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"LocID", "Country-ID"}, {"Location", "Country"}, {"Time", "Year"}, {"AgeGrp", "Age-Group"}, {"PopMale", "Population-Male"}, {"PopFemale", "Population-Female"}, {"PopTotal", "Population-Total"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","9-May","5-9",Replacer.ReplaceText,{"Age-Group"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","14-Oct","10-14",Replacer.ReplaceText,{"Age-Group"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value1",{{"Year", Int64.Type}, {"Population-Male", type number}, {"Population-Female", type number}, {"Population-Total", type number}}),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type", {"Country-ID"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Errors", each ([#"Country-ID"] <> null)),
#"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"Country-ID", Int64.Type}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type1", each true),
#"Changed Type2" = Table.TransformColumnTypes(#"Filtered Rows1",{{"Year", Int64.Type}}),
#"Filtered Rows2" = Table.SelectRows(#"Changed Type2", each true),
#"Removed Errors1" = Table.RemoveRowsWithErrors(#"Filtered Rows2", {"Population-Male"}),
#"Removed Errors2" = Table.RemoveRowsWithErrors(#"Removed Errors1", {"Population-Female"}),
#"Removed Errors3" = Table.RemoveRowsWithErrors(#"Removed Errors2", {"Population-Total"}),
#"Filtered Rows3" = Table.SelectRows(#"Removed Errors3", each ([#"Country-ID"] <> null and [#"Country-ID"] <> "")),
#"Removed Blank Rows" = Table.SelectRows(#"Filtered Rows3", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
#"Filtered Rows4" = Table.SelectRows(#"Removed Blank Rows", each true),
#"Trimmed Text" = Table.TransformColumns(Table.TransformColumnTypes(#"Filtered Rows4", {{"Country-ID", type text}}, "en-GB"),{{"Country-ID", Text.Trim, type text}}),
#"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Country-ID", Text.Clean, type text}}),
#"Changed Type3" = Table.TransformColumnTypes(#"Cleaned Text",{{"Country-ID", Int64.Type}}),
#"Filtered Rows5" = Table.SelectRows(#"Changed Type3", each true),
#"Trimmed Text1" = Table.TransformColumns(Table.TransformColumnTypes(#"Filtered Rows5", {{"Country-ID", type text}}, "en-GB"),{{"Country-ID", Text.Trim, type text}}),
#"Filtered Rows6" = Table.SelectRows(#"Trimmed Text1", each true),
#"Replaced Value2" = Table.ReplaceValue(#"Filtered Rows6","",null,Replacer.ReplaceValue,{"Country-ID"}),
#"Changed Type4" = Table.TransformColumnTypes(#"Replaced Value2",{{"Country-ID", Int64.Type}})
in
#"Changed Type4"