Excel files 'null' when closed but with data when opened

[FloK]

Hi all,

I am importing several hundred Excel files from a folder into Power Query. Unfortunately, for some sheets within every file Power Query shows 'null' only. When I open the sample file and refresh the preview, it shows data. When I open the file and save it without doing anything (quick save), the file works fine. All others do not unfortunately.

I'm aware of a similar post that solved this problem by saving the file in proper .xlsx format. Unfortunately, this is not an option for me since I don't have editing rights on the folder in question.

However, I'm not even entirely sure it's an issue of the file itself. It is already saved as an .xlsx. The only thing I need to do is quick save it. Do you have any ideas how I could solve this?

[FloK]

Hi Pat,

Thanks for the tip. Unfortunately, I'm still confronted with the same problem.

When I go to 'get data' --> 'from folder' and on the next window go to 'transform data', this is what I'm seeing:

However, when I open the files and refresh the preview, it recognizes column headers and is filled with data.

If I close the files and hit refresh, I'm back to nulls again.

[ppm1]

I suspect something is named differently in the files showing null. Look at your M code in the Advanced Editor in the query editor and look for specific references to named things (files, tabs/worksheets, tables, etc.). Note that Power Query is case sensitive.

Pat