Hi @Srinivas_BI
The "ORA-01722: invalid number" error typically occurs when Oracle encounters non-numeric data in a column where a numeric value is expected. Here are steps to help troubleshoot and resolve this in your Power BI Dataflow:
1. Identify the Column with Mixed Data Types:
Look at the columns involved in your query, particularly those expected to be numeric. Check if any of them contain text or other non-numeric data that could cause this error.
2. Use the 'TO_NUMBER' or 'CASE WHEN' Statements in SQL :
Wrap potentially problematic columns in a 'TO_NUMBER' function or a 'CASE WHEN' statement to handle non-numeric values, e.g.:
CASE WHEN REGEXP_LIKE(column_name, '^[0-9]+$') THEN TO_NUMBER(column_name) ELSE NULL END
This will replace non-numeric values with NULL or another default numeric value, preventing the error.
Best Regards,
Jayleny
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
