Appending query: OLE DB or ODBC error: [DataFormat.Error] Unspecified error

[MJ1991]

Hi Power Bi Community,
First post on my end - so far looking through your forums really helped me with all my issues.
However on the one already explained OLE-DB-or-ODBC-error-DataFormat-Error-Unspecified-error/td-p/757828  by @Anonymous , I couldn't find any helpful information online in any forum whatsoever.
I am facing the same issue: Trying to append two tables A and B, which itselves are appended tables already (from A_1, A_2 and B_1, B_2).
Appending A_1, A_2 into A and B_1, B_2 into B works fine, however appending A and B leads to the stated error "OLE DB or ODBC error: [DataFormat.Error] Unspecified error" I tried clearing the cache, reopened the file, added "remove error" steps to A_1,A_2, B_1, B_2 as well as A and B, however nothing works for me. A_1, A_2, B_1 and B_2 are reduced subsets of bigger tables which also contains date fields (however as stated the first appending steps are working fine).

All tables only contain 2 columns with text in it.
Ultimate goal is to create a distinct key for all tables.
In addition, the query is loading >7 Mio. rows with 2 colums for the append query in case it is related to a limitation of Power Bi. Any ideas on what I could try to get this fixed or at least where it comes from?

Thanks a lot in advance!

MJ

[swise001]

@MJ1991 

You'll have to trace back each table to it's origin in Power Query - and try to identify which one is holding the error.  Imagine a scenario where a letter is in a column of numbers that get's marked as Number.  This will throw an error.  If that column is later changed back to text - the error will persist.   With so many rows - it's likely not showing up in the 'preview' because of the sampling that Power query does to provide the previews.  

If that doesn't work - consider trimming and cleaning your columns and text formats and verifying that everything is being saved as 'text' very early in it's import life. 

[MJ1991]

Hi @swise001 ,

Thanks for the reply!
I recreated the model, placed all files locally but the error persists.

Regarding your suggestion:
I removed the step where powerbi suggests a data type and directly put the type I assume to be correct.
Three follow-up questions on my end here:

1) In case it is a data type issue, why isn't PowerBi stating the corresponding error (instead of unspecified error)?
2) Sometimes (1/10 chance) my query finishes without error. Is it sometimes by accident skipping the error lines?

3) I have applied "remove error" steps on all files, shouldn't those catch the error?

Best regards & thanks in advance!

[swise001]

@MJ1991 

Using the method i described above - I appended two tables (one that contained an error) - and this is what i learned: 

1.  With an error in the column - Power BI still imported the query to the model - (it just removed the error values - and left the cells blank).  I didn't get any alerts...

2.  However, if I sorted the column with an error in it I get this

Notice the text is similar - but a little different from yours. 

You may have to keep working with the source data to try and isolate the table that is causing the problem.  Since your alert says "unspecified" it's not clear what part of it is causing the issue. 

[Greg_Deckler]

@MJ1991 - Extremely difficult to troubleshoot. You could check the Issues forum here:

https://community.powerbi.com/t5/Issues/idb-p/Issues

And if it is not there, then you could post it.

If you have Pro account you could try to open a support ticket. If you have a Pro account it is free. Go to https://support.powerbi.com. Scroll down and click "CREATE SUPPORT TICKET".