Power Query Combine Binaries messes up data

Question

I'm combining a number of files from folder in Power Query 2013. For some reason, it adds the names of the first 2 columns to some random rows in the last two columns, or even replaces data with other column name.

First, I thought it's due to difference in column schemas (some files didn't have the last column). I aligned all files, but the issue didn't go away.

Answer 1

Combining binaries in Excel can cause formatting problems. To understand the problem, you'd probably need to share your files (you could remove any sensitive data, but include enough columns to reproduce the problem).

---

Alternatively, if you use Power BI Desktop there's a new feature for combining binary files in a safer way.

Answer 2

I have found a workaround by ensuring the CSV files have no blank cells in them. This removed most of errors.

Still, sometimes it adds the first column's name to the last cell of each file, but that can be fixed by replace value.