Forum Discussion

CCHarrison's avatar
CCHarrison
Regular Visitor
2 years ago

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.

Need Help

7 Replies

  • BA_Pete's avatar
    BA_Pete
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      CCHarrison
      Regular Visitor
      2 years ago

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

    • CCHarrison's avatar
      CCHarrison
      Regular Visitor
      2 years ago

      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's avatar
        spinfuzer
        Icon for Solution Sage rankSolution Sage
        2 years ago

        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?