Hidden Columns Are Causing Errors

[Stacker]

So I am using power query to join multiple tables, across identical sheet, across multiple files. To do this i used power query and when I joined the files I used =Excel.Workbook(Content). It works perfectly when they are no hidden files, however there are some files where column A is hidden, but other than that the table is identical. The first two files don't have Column A hidden and the appending worked perfectly, however the third file has Column A hidden and the hidden column turns up in the power query and the whole table moves to the right. How do i fix this error? I accidentally hit enter to soon so apologies for ambigious title.

 

[Alex Blakenburg]

Try this.

Using Get Data > From File > From Folder

Select a file without the hidden column as a sample file.
In the Transform Sample File

• Take the necessary steps to promote the headings.
• if it creates a step Changed Type - DELETE IT

In the final Merged Query (Most likely have the name of the Folder you are accessing.

• DELETE the Changed Type step
• Your last step is now more likely "Expanded Table Column"
• Now create your own Changed Type step
  • Click anywhere in the table area and hit ctrl+A to select all
  • Transform > Detect Data Type
  • Review the column data types and change any you are not happy with

In this process the automatically created Changed Type steps, hard code column headings that are not the final column headings.
Remove the Changed type steps until you have finalised all the other steps and then reapply on the final output.