Navigation step failing to load sheet from workbook

[CCHarrison]

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.

[BA_Pete]

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

[CCHarrison]

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... 

[spinfuzer]

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?

[CCHarrison]

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.

[spinfuzer]

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.

[CCHarrison]

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. 

[CCHarrison]

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!👍)