Error returned: 'OLE DB or ODBC error: [DataFormat.Error] We couldn't convert to Number.. '.

[Anonymous]

Hi All,

I am getting this error when applying my query. However data loading in the query editor does work. Only when applying the query I get this error. 

I have read that this error may be caused by applying data conversion.

But I don't understand where it comes from;

Followng steps I apply in the query editor

• Source (is linked to another table)
• Append Query
• Filtered Rows
• Renamed Columns
• Changed Type (from any ABC123 to Percentage)

let
    Source = #"POC Change STAGE",
    #"Appended Query" = Table.Combine({Source, #"Project STAGE"}),
    #"Filtered Rows" = Table.SelectRows(#"Appended Query", each [value_new] <> null and [value_new] <> ""),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"value_old", "POC_OLD"}, {"value_new", "POC_NEW"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"POC_NEW", Percentage.Type}})
in
    #"Changed Type"

Is it also possible that the error comes from the source in which already other queries are applied? Or is it strictly this query where the error comes from?

Please let me know if you need more info.

Thanks!

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

The error is caused that there are alphanumeric values in the column "POC_NEW", like "ABC123" that you said. They couldn't totally get converted. You need to stay the data type column to text. If you want to other format, you could apply it and then create formulas using FORMAT function to re-define it.

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Apparently there was an error row in the column. PowerBi showed me this by using the column header with a small red part and a white and green part.