DataFormat.Error: There were more columns in the result than expected.

[Philippe de Vlaminck]

Dear,

I am currently trying to merge several data tables into one in order to create a dashboard.
One of the datasets is being a pain in the *** since it was not dumped properly and has some anomalies. I have included one of the files here: https://drive.google.com/file/d/0B7PlYvW7IcVndDJQVGtsLXNRYkk/view?usp=sharing
When you scroll down to about the middle you can see the difference in the dump.

Following issue:
When I try to merge this table with other tables I encounter an error:
DataFormat.Error: There were more columns in the result than expected.
Details:
Count=43

I am not that experienced with power querry but would expect the difference in data to be the rootcause.
Following I have tried:

• I have found some posts about how to remove this problem when occurring in the first line with the transform > delimiter option but this does not work for me.
• Filtering the first column to not include the 'date rows' worked but upon merge error occurred again

Any idea's on how to solve this issue would be more then welcome

Kind regards,
Philippe

 

[Philippe de Vlaminck]

Update: During sorting of this dataset the error occurs also.

 

[Philippe de Vlaminck]

I have fixed the data anomalie issue but the problem persist. Any ideas would be more then welcome, havnt found a solution yet

 

[Philippe de Vlaminck]

Hey all, I was able to find a solution:
I imported the files as text files, after which not all data was properly split, only into 4 columns. I then used the delimiter option for a column split, evrything works fine now.