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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
CCHarrison
Regular Visitor

Navigation step failing to load sheet from workbook

Hi,

 

I'm a bit stuck here, because I can't attach the actual data sheet this is happening with (it is proprietary, unfortunately). I'm hoping someone recognises the issue from my description, and can give me an idea what might be happening...

 

I have an Excel resource that uses power queries to import data from a range of .csv and other .xlsx resources. The issue I'm seeing does not seem to happen consistently, even though the data sources, a priori, are always identical in terms of layout.

 

What's happening is, the Navigation step

 

= Source{[Item="XXX",Kind="Sheet"]}[Data])

 

in the import of an .xlsx resource is returning a blank sheet, even though the Sheet in question ("XXX") does contain data. I am not getting an error, at least not at this step, (but I am later in the query, as subsequent references to elements of the Sheet fail, as there's no data there).

 

The Sheet name is correct (no spaces or anything like that). If I introduce a step to change the sheet name to another sheet in the same workbook, the Navigation step works, but when I change it back to the sheet I want, it returns a blank sheet again.

 

In case it's important, the previous import cue that grabs the whole workbook is:

 

Source = Excel.Workbook(Web.Contents(FilePath & "FOLDER/DOC.xlsx"), null, true),

 

 

Thanks for your help.

7 REPLIES 7
BA_Pete
Super User
Super User

Hi @CCHarrison ,

 

Difficult to say with any certainty without being able to see the setup first hand, but I'd suggest trying the following in the first instance:

 

-1- Refresh the preview: In Power Query, go to the Home tab > Refresh Preview. The query previews are cached on your HDD, so this will force an update.

-2- Shortcut the sheet reference: Try referencing the offending sheet by item position in the workbook, rather than by name (in case there's special chars you can't see etc.). Use the following, where the number is the sheet position in a zero-base list:

= Source{0}[Data]    // References 1st sheet in workbook
= Source{1}[Data]    // References 2nd sheet in workbook

// etc.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Hi Pete,

Referencing the sheet directly as you suggested produces the same outcome: 

= Source{0}[Data]

returns a blank sheet (even though there is definitely data in it, and, as I said, I update these resources regularly, this doesn't always happen, and a priori this workbook does not change format). 

= Source{1}[Data]

returns the second sheet, as expected... 

Are you sure there is not some type of NA() or other error on your sheet before importing into Power Query?  Is your query potentially returning blanks when there is an error on the sheet?  Can your force an error onto a sheet that you know works and then see if it still works after you type something like =NA() into a cell in a column that definition gets transformed?

Hey resolver! Thanks for the suggestions. I am not sure of that and will check when I get a chance. Can I just bounce this back to you though, does it sound likely to those of you who know Power Query better than me that an error in a cell on a data sheet would produce this kind of behaviour in Power Query (i.e. returning a blank sheet without throwing an error)? Has anyone seen that before? Thanks.

Truth is I am not sure.  What we are trying to figure out is what is different about the sheets.  Does one contain errors and other doesn't?   Does one have data in a different format? Can you force a scenario that will reproduce the same blank issue on a worksheet that otherwise works?  It's hard to help troublesheet if we do not have sample data.

Yes, I know not being able to see my actual sheets makes it really hard for you work out what's going on, which is why I greatly appreciate the feedback you're giving me. I'll reply here once I've had a chance to explore the offending sheet in more detail. Cheers. 

Thanks Pete. Had already tried refreshing the preview. No avail. Direct referencing the sheet sounds like a good strategy. I'll give it a shot (on Monday!👍)

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors