Power Query in Excel: Importing data from a folder but some files won't expand all columns with "expand table columns"

Question

Hello, all.

I am pulling data in from a folder full of spreadsheets. Everything was working fine until I got my latest batch of files. All the files I have ever used are the exact same exports from JIRA so the columns for all of them are identical. Someone compared a new and old export and confirmed that the column names all seem identical. I had been able to add new files and even updated versions of the same files to the folder and everything worked fine. But now I have newer, updated versions and it doesn't work!

What happens with my old files:

• Expand table column and all columns from the spreadsheets show up
• 

What happens with my new files:

• Expand table column and only the first column from the spreadsheets shows up
• 

I am stumped. Does anyone have any idea what on earth could be going on and how to fix it???

Answer 1

In case anyone is looking at this and has the same question, I finally found the answer:

It is a problem with metadata that is incorrectly added sometimes when you do an export to Excel from other programs. The dimensions attribute gets messed up and tells PowerQuery that your data is a lot smaller than it really is by providing an incorrect start and end cell. In my case it was showing only 1 column and 5 rows.

There are a few solutions to this:

• fix the export to not mess this up
• simply resave the exported file once you export it (what I'm doing)
• edit the M query to tell it not to trust the sheet dimensions metadata and figure it out for itself (InferSheetDimensions = true)

For the last option, I only see examples of how to do it for an individual file rather than when pulling in a whole folder of files but maybe that's possible somehow, too.

Here's a link to the solution:

https://docs.microsoft.com/en-us/power-query/connectors/excel#troubleshooting

Answer 2

Hi,

Follow the steps in this video- (1) Power Query #08: Import Multiple Excel Sheets From Multiple Excel Workbooks.

Answer 3

This doesn't speak to my issue at all, unfortunately. My issue is that when I get to the step of creating the custom column that is supposed to contain all of the data from my spreadsheets and I expand that column, it only shows ONE of the many columns in my spreadsheets.

The issue I need help with is what could be causing it to only find the first column.

Thanks.