Dataflow failing to refresh

Hello Srinivas_BI ,

 

An ORA-01722 error occurs when an attempt is made to convert a character string into a number, and the string cannot be converted into a number.
oracle database - SQL error "ORA-01722: invalid number" - Stack Overflow

 

Kind Regards,
Gökberk Uzuntaş

📌 If this post helps, then please consider Accepting it as a solution and giving Kudos — it helps other members find answers faster!

🔗 Stay Connected:
📘 Medium |
📺 YouTube |
💼 LinkedIn |
📷 Instagram |
🐦 X |
👽 Reddit |
🌐 Website |
🎵 TikTok |

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.