Get Data from workbook - Tables not listed

Power Query
1

I have an external workbook with many named tables. I can navigate to them in excel w using Goto (f5).
In a new workbook I go Data Get Data. From Workbook etc.

I get a list of tables and ranges on the external book but the tables I could navigate to are not listed.

Can anyone suggest the likely cause and / or a fix.

Thanks

Allister

2

Hi @AllisterB,

This is an Excel related question, please post it to an Excel Forum.
The focus on this forum is Power BI

Thank you!
Donā€™t forget to close this thread by marking it as solved.

3

Sorry about this @Melissa

I believed it was a Power Query Question as it related to Get Data.

Regards

Allister

4

ā€¦from within Excel, right?

5

@Melissa
Yes.

Most of my work at the moment needs to be in Excel as I am developing decision making Models for others to use.

Regards

Allister

6

@AllisterB, I am unable to recreate your problem - and Iā€™m not finding a solution using Google.

My testing involved adding a table to a large file with over 60 worksheets - closing the file - attempting to connect to the file from a new workbook. The import screen showed the table at the top of the list, and all of the sheets below that (differentiated by the icons).
image
The same thing happened when I tried to use the ā€˜from workbookā€™ command within the open file (instead of from a new file)

But a few questions occurred to me as I was testing, and these might give you some avenues of investigation:

  1. do you have any sheets that have more than one table?
  2. how many sheets and/or tables are in the workbook?
  3. are your tables named the same thing as the worksheets? (should not be an issue, but worth testing)
  4. are you sure that you are looking at tables in the workbook and not named ranges? They look identical in the GoTo window:
    image
    but named ranges donā€™t show in the ā€œGet Dataā€ import window
2 Likes
7

Hi @Heather

It just occurred to me that the tables I was missing were on hidden sheets. Using Power Query I have been able to see the tables I wanted to - they were on Hidden (Not VeryHidden) sheets. The sheets need to be hidden. I unhid the Sheet - made the connection and then hid thesheet again and the query works.

Thanks for getting me to think outside the square

Kind Regards

Allister

2 Likes
8

@AllisterB glad my response helped you to find a solution

Iā€™ve noted this for any future connection issues I might have :slight_smile: