Source column contains empty strings or special characters (e.g. 'N/A', '-', '') that cannot be cast to INT or DECIMAL. ADF maps a source VARCHAR column to a target INT column without a transformation step. A data quality change in the source introduced text values in a formerly numeric column

Step 1: Find the bad values: SELECT col FROM source WHERE TRY_CAST(col AS INT) IS NULL AND col IS NOT NULL;. Step 2: In ADF Data Flow, use a Derived Column with TRY_CAST to replace unconvertible values with NULL or a default: iif(isNull(toInteger(col)), 0, toInteger(col)).. Step 3: Fix the source data quality: work with the upstream team to prevent non-numeric values in numeric columns, or add a CHECK constraint on the source.

Medium severitydata integrity

Power BI Refresh Error:
245

What does this error mean?

SQL Server cannot implicitly convert a string value to a numeric type because the string contains non-numeric characters.

Common causes

1Source column contains empty strings or special characters (e.g. 'N/A', '-', '') that cannot be cast to INT or DECIMAL
2ADF maps a source VARCHAR column to a target INT column without a transformation step
3A data quality change in the source introduced text values in a formerly numeric column

How to fix it

1Step 1: Find the bad values: SELECT col FROM source WHERE TRY_CAST(col AS INT) IS NULL AND col IS NOT NULL;
2Step 2: In ADF Data Flow, use a Derived Column with TRY_CAST to replace unconvertible values with NULL or a default: iif(isNull(toInteger(col)), 0, toInteger(col)).
3Step 3: Fix the source data quality: work with the upstream team to prevent non-numeric values in numeric columns, or add a CHECK constraint on the source.

Frequently asked questions

What is TRY_CAST and how does it differ from CAST?

TRY_CAST returns NULL if the conversion fails, instead of raising an error. Use it to safely handle mixed-type columns: SELECT TRY_CAST(col AS INT) FROM table — NULL rows are the unconvertible values.

Can error 245 appear in Power BI DirectQuery mode?

Yes — if a DirectQuery DAX measure generates SQL with an implicit conversion, error 245 can surface as a Power BI query error. Switch to explicit CAST in the calculated column or measure.

How do I find all rows that would fail conversion?

Run: SELECT col FROM table WHERE TRY_CAST(col AS INT) IS NULL AND col IS NOT NULL; — this returns all rows that cannot be cast to INT without failing.

Official documentation: https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-245-database-engine-error