Excel Forum

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

Sebastian Fuentes

Active Member

Members

Forum Posts: 5

Member Since:
April 8, 2021

Offline

April 8, 2021 - 6:54 am

Hello All,

I am picking .CSV files from a folder through Power Query (Name: "Traslados_Envia_csv"). Recently the owner of the .CSV Files has added a new Column ("Column 9") with some new data. Once I tried to refresh the Query it shows the following error "[DataFormat.Error] There were more columns in the result than expected" (This error disappear if I delete this new Column manually. However these data has to be keep it)

Error

When I go to the query I have tried to modify the Code in the "Advance editor" by trying to add the Column manually but the problem is not solved. See below:

Original Line

#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8"}, {"Custom.Column1", "Custom.Column2", "Custom.Column3", "Custom.Column4", "Custom.Column5", "Custom.Column6", "Custom.Column7", "Custom.Column8"}),

Modification

#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9"}, {"Custom.Column1", "Custom.Column2", "Custom.Column3", "Custom.Column4", "Custom.Column5", "Custom.Column6", "Custom.Column7", "Custom.Column8", "Custom.Column9"}),

It seems that they way that I build the models does not work with dynamic columns ?

Please find Enclosed:

File where the query is done: "BOLSA_VS_GUIAS_ENVIA _V2" (The query is in the Tab "Traslados")

Raw File: "34969_ORIGINAL_$1.169.797" (The file with the new Column that causes the problem)

Raw File "32295_01032021_$1.139.200" (Sample file with the old layout, wich with the Query works)

Additional notes:

The File has two additional Querys more. The A That pulls .xls file from the same folder where Query B (causing the problems) pulls the . CSV files and a C Query that joins Query A and B.
If you want to replicate the exercise please check that the query is looking for the location of the folder in the Tab "DyRefs" cell "K4"

I have done some research but I have not found a proper solution.

Thanks in advance.

See Answer

Sebastian Fuentes

Active Member

Members

Forum Posts: 5

Member Since:
April 8, 2021

Offline

April 8, 2021 - 6:57 am

Sorry I am new in the forum and forget to upload the files 🙂

Philip Treacy

Admin

Forum Posts: 1529

Member Since:
October 5, 2010

Offline

April 8, 2021 - 12:05 pm

Hi Sebastien,

I'm not clear on whether you are now trying to merge files where some have 8 columns and some have 9?

Looking at your queries it looks like you are adding a Custom Column and then expanding that column to get the file contents. You should be using the Content column and using the Combine Files icon - see attached image

I've duplicated your queries but as I don't have any XLSX files I can't load or combine them with the CSV. I've moved your queries into a group called Original Queries with Errors and my queries and in the Other Queries group - see attached file.

In the query that loads the CSV I have used the Combine Files icon, then removed all unnecessary columns. I can't do the transforms as I don't know what you need to do.

Please read this post to see how to Combine Files from a folder

Power Query Get Files from a Folder • My Online Training Hub

and if you are still having issues post back here.

Regards

Phil