ODBC problem in loading data

[Anonymoussss]

Hi,
Got this error

Expression.Error: Data source error occurred.
SQLSTATE: 22018
NativeError: 0
Error message: ODBC: ERROR [22018] [MongoDB][API] invalid character value for cast to type: Bit

I use MongoDB Atlas SQL connector to get the data. MongoDB ODBC is being used.
The column is of type true/false. there is no error in data(Also no null values). The problem occurs exactly at the 1006th column(the table has around 1300 columns). If i remove this particular column, then the complete table with all the rows load. I tried changing the type of the column to text but same error persists.

Whats the problem and how do I solve it.

[v-vpabbu]

Hi @Anonymoussss,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Vinay Pabbu

[v-vpabbu]

Hi @Anonymoussss,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,
Vinay Pabbu

[v-vpabbu]

Hi @Anonymoussss,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Vinay Pabbu

[v-vpabbu · Accepted solution]

Hi @Anonymoussss,

Thank you for reaching out to Microsoft Fabric Community Forum.

I would suggest considering the following approach to resolve the issue

• Instead of loading the entire dataset at once, try excluding the problematic column initially. Once the rest of the data is loaded, add the column separately and check if the issue persists.
• If possible, use the native MongoDB connector in Power BI instead of ODBC. The native connector is optimized for MongoDB and may handle Boolean values more effectively, avoiding the casting error.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Regards,
Vinay Pabbu

[AnkitKukreja]

Hi! @Anonymoussss 

When you say this column, what datatype does it have in source and which datatype are you using in PBI?
Try to convert that column at the source level. The datatype conversion within the query itself should help . Also, try calling that one column and see if that works or not, this way you can narrow down the problem.

If this doesn't solve your problem you can raise a ticket - If you have a Pro license you can open a Pro ticket at https://admin.powerplatform.microsoft.com/newsupportticket/powerbi
Otherwise you can raise an issue at https://community.fabric.microsoft.com/t5/Issues/idb-p/Issues.

[Anonymoussss]

Hi @AnkitKukreja 

I think the problem is with the MongoDB atlas sql connector. I've tried loading that particular column separately and the error raises. If I ommit that column, then other columns load. The data is getting loaded if I download and use but I need latest data so I use import mode and refresh the data as using direct query does not support some transformation steps.

Thank You.

[v-vpabbu]

Hi @Anonymoussss,

Since the issue is with the MongoDB Atlas SQL Connector, and the error occurs when loading just that column, the problem is likely due to how the ODBC driver handles Boolean values.

Some ODBC drivers struggle to interpret Boolean data types correctly, leading to type mismatch errors.

Try converting the column at the source level before it reaches Power BI.

Regards,

Vinay Pabbu