Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
FloK
Regular Visitor

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

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?

2 REPLIES 2
FloK
Regular Visitor

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:

 

FloK_2-1687868927001.png

 

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

 

FloK_1-1687868887369.png

 

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

ppm1
Solution Sage
Solution Sage

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

Microsoft Employee

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors
Top Kudoed Authors