Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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

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

General Comment
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous 

    Can you provide some sample picture after appending the tables?

     

    Best Regards!

    Yolo Zhu

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        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's avatar
    Anonymous
    Not applicable
    2 years ago

    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