Failed to save modifications to the server 'OLE DB or ODBC error: and Failed to convert to Int

[Michelle-M]

Hi, I have read through other posts but cannot find this specific type of post as I'm using a Cbase Couchbase ODBC connection. 

When I'm in Query Editor, I can identify that row 721 is coming up with the error and in particular column 'Code' which was initially an Int value, cannot convert an alphanumeric value to Int.  I have looked at the data in the table and there are other alphanumeric values as well, however I'm guessing this is the the first value that it might be encountering?

- I've changed the column type to 'Text'

- I've tried to 'replace errors' 

- I've tried to replace values as well

None of the above is working.  What am I missing in order to solve this problem? 

I'm using a test sample to connect to, but when I've tried to connect to another server, I'm getting the same type of errors, different coluumns. 

I can solve the problem by deleting the column but that is not solving the problem, that's just removing information.  I would really like to know how to solve this.  Your help is much appreciated.

I'm trying to attach the .pbix file for reference, do I do this via Publish and share the link?

[amitchandak]

@Michelle-M , check the steps. You have to change it to text on or before the step it was converted to int.

Change data type step might be there as a second or third step, make sure it is text in that step. Make sure it did not get converted to int

[Michelle-M]

Thank you @amitchandak for your information.  The original dataset is in Int and in the query editor, I've changed it to Text.  That's when I try and Close & apply and get the error.

Are you indicating I need to change the column from Int to Text at the server end before I attach the ODBC Couchbase source?

[amitchandak]

@Michelle-M , Usually when add datasource, the power query automatically adds a step to change the data type, and that needs to be overwritten in such cases.  

Another way is to add this table as a new one and check does it load. if yes, carefully replace the top part of your old query with new query(Power Query code)

[Michelle-M · Accepted solution]

@amitchandak  Thank you again for your reply.  

I have solved this problem by creating a N1QL query, saving the .json file and using the .json file as the datasource, rather than the Couchbase ODBC as the datasource.

The .json file has allowed me to change the datatype whereas previously I was not able to do that and I have been able to remove the errors