Hello everyone,
I am trying to consolidate data with Power Query. I use get Data --> From Folder.
Each .XLS (97-2003 format) sheet that I am trying to consolidate have the same structure. So in the "Transform Sample File" section, I have applied the following steps:
- The top 7 rows are deleted
- My header names are in three (3) rows so I have transposed them, merged and named them
- I then use the merged headers as "Use First Row as Headers"
I then go into Other Queries, in the hope that my files are consolidated but I am left with the error: "An error occurred in the ‘Transform File’ query. Expression.Error: The column 'Au_ppm_Au-GRA22' of the table wasn't found.
Details:
Au_ppm_Au-GRA22"
I have managed to consolidate data when all the columns are the same. The problem with my data is that sometimes I have extra columns that appear, and Power Query doesn't like the fact that some columns are in certain XLS files and others are not. I have tried using my reference file as the one with the most columns but that doesn't seem to be the answer. Since the rest of my data are missing certain columns I get the following error.
