What causes CAST_INVALID_INPUT?

Casting a STRING column that contains non-numeric values (e.g., 'N/A', '') to INT or DOUBLE. Casting a malformed date string to DATE or TIMESTAMP (e.g., '2024-13-01' or '01/15/2024'). An upstream pipeline change introduced new string formats or sentinel values into a numeric column. Implicit casting in a JOIN condition between incompatible types. A CSV or JSON source file containing unexpected null representations that fail type conversion

How do I fix CAST_INVALID_INPUT?

Use TRY_CAST(col AS INT) to return NULL instead of failing on invalid input. Filter out or transform invalid values before the CAST: WHERE col RLIKE '^[0-9]+$'. Inspect sample values: SELECT DISTINCT col FROM table WHERE TRY_CAST(col AS INT) IS NULL AND col IS NOT NULL. Fix the source data or upstream pipeline to produce clean, correctly typed values. Add a data contract or schema enforcement step at the ingestion layer to catch type issues early

Medium severitysql

Power BI Refresh Error:
CAST_INVALID_INPUT

What does this error mean?

A CAST or implicit type conversion failed because the input value cannot be converted to the target data type. The query is aborted — no partial conversion is performed.

Common causes

1Casting a STRING column that contains non-numeric values (e.g., 'N/A', '') to INT or DOUBLE
2Casting a malformed date string to DATE or TIMESTAMP (e.g., '2024-13-01' or '01/15/2024')
3An upstream pipeline change introduced new string formats or sentinel values into a numeric column
4Implicit casting in a JOIN condition between incompatible types
5A CSV or JSON source file containing unexpected null representations that fail type conversion

How to fix it

1Use TRY_CAST(col AS INT) to return NULL instead of failing on invalid input
2Filter out or transform invalid values before the CAST: WHERE col RLIKE '^[0-9]+$'
3Inspect sample values: SELECT DISTINCT col FROM table WHERE TRY_CAST(col AS INT) IS NULL AND col IS NOT NULL
4Fix the source data or upstream pipeline to produce clean, correctly typed values
5Add a data contract or schema enforcement step at the ingestion layer to catch type issues early

Frequently asked questions

What is the difference between CAST and TRY_CAST?

CAST raises CAST_INVALID_INPUT if the value cannot be converted. TRY_CAST returns NULL instead of raising an error, making it safer for dirty data.

Can I log which rows failed the cast?

Yes — use TRY_CAST in a SELECT and filter WHERE TRY_CAST(col AS type) IS NULL AND col IS NOT NULL to find all rows with unconvertible values.