Type mismatch error when appending tables, works with integers, but not with strings

[Anonymous]

Hello,

This is my first post, so I apologize in advance if I made any mistakes.

I have two tables that have identical columns with matching data types. However, when I try to append them together in Power Query, I encounter the following error: 'OLE DB or ODBC error: Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))'. Interestingly, when I work within the Power Query Editor, none of the columns show errors, and the tables appear to append correctly. The error only occurs during the 'close and load' process.

Furthermore, if I convert a column to a whole number, then remove errors, and then convert it back to text (essentially deleting records with text values), everything works fine. I don't understand why it won't append text columns unless the text column in the second table contains only integers.

Does this behavior ring a bell with anyone?  I really appreciate your attention.

Thanks,
Kurt

[Anonymous]

The problem turned out not to be part of the append process, as I initially thought. Upon trying to recreate the issue, I discovered that the error was generated downstream of the append. Ultimately, I started over from scratch and resolved the issue. I appreciate everyone's attention.

Thanks,
Kurt

[Anonymous]

Hi @Anonymous 

Based on your description, your column has string and whole number, did it like the following picture?

If it like this, you can keeo the original column, then put the number to a new column, then use the new column to append. you can refer to the following code.

try Number.FromText([Column1]) otherwise null

And you can refer to the following similar thread.

Solved: OLE DB or ODBC error: Type mismatch. (Exception fr... - Microsoft Fabric Community

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks for your reply. It seems to suggest creating a new column that ignores the text rows by nulling out non-numeric values. However, I need to retain the text values during the append process. Hopefully, the following will help clarify the problem:

Table A

Table B

Append result

[Anonymous]

The problematic column is "VEHICLE." If I eliminate all rows with text in that column, the append operation works as expected. However, we cannot ignore vehicles with text in their identifiers.

[Anonymous · Accepted solution]

Hi @Anonymous 

Can you provide some sample picture after appending the tables?

Best Regards!

Yolo Zhu