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’m using MongoDB Atlas SQL connector in Power BI (Import mode). A particular true/false column in not loading after 1005 rows(around 1350 rows in table). No error in data.No null values.(when downloaded the data and opened with excel, all rows load). when that particular column is excluded, all rows load. What is the issue here and how do i solve it.
Note: I’m using latest version of Power BI and MongoDB Atlas SQL ODBC Driver.
Hi there and welcome to the community. My first thought is that there might be a data type mismatch in the sql schema and what is in your database. Since MongoDB can store different data types within the same field, for example, this can be a problem if the sql schema says it’s an int and then when loading it encounters a string. You could review the sql schema from the Atlas Data Federation Instance then “mange sql schema” button. From there you can regenerate the schema, but that method can sometimes pull in a small sample. You also have the option to generate the schema using a large sample size, but this operation is done through mongosh. https://www.mongodb.com/docs/atlas/data-federation/query/sql/schema-management/#schema-management
A tool like excel doesn’t need to id the data type in such a rigid manner, hence allows you to load it in. But within Excel, you might be able to identify the data issue.