Importing multiple Tables from Excel file

[jcmayernik]

Hi!

I have a large Excel workbook with many worksheets and Tables. This is the "source of truth" for a report I'm building in Power BI.

However, when I attempt to use the data in this Excel file, the "Get data" Navigator is only showing some of the Tables as available. Even the "Suggested Tables" section does not have all the available Tables showing.

How can I get to the rest of the Tables? 

If it makes a difference, this is an Excel file with macros. (No, I cannot share it, it contains proprietary information.)

Some additional information - there are sheets that are hidden when this file is opened. It does appear that the Tables that show as available immediately are on a sheet that is visible all the time.  However, the Tables that show in the "Suggested Tables" section are on some of the hidden sheets. There are 14 hidden sheets with one Table each. At the very bottom of the "Suggested Tables" section, it shows 4 of them, but it appears to have chosen which 4 randomly. (It also doesn't call them by their name, and they are named Tables.) The "Tables" it shows at the top of the "Suggested Tables" section are not actually Table objects - they are data ranges or PivotTables, and are not named. Not sure why Power BI is assuming those are tables.

Screenshot of what I see when I click "Get data" and select the Excel file:

Updated to add: Leaving all the worksheets unhidden appears to solve the problem - except those sheets are hidden for a reason, and I would prefer not to have them out where other users could accidentally break them.

[DataInsights · Accepted solution]

@jcmayernik,

In your screenshot, right-click the top folder (workbook name) and select Transform Data. From there, you can click on the desired tables to import.

[jcmayernik]

Thanks for your quick response! Also, this will sound like an ignorant question - I am very new to Power BI and have been working from other people's reports, so I have zero training on how Power BI works. (And some very limited PowerQuery experience.)

When I followed your instructions, Power BI opened up the Transform Data window, and it does have a new query that lists all the Tables in that file. (Awesome!)

But now how am I supposed to get those in as source for my data? Do I have to code the query in?

[DataInsights]

@jcmayernik,

Click the "Table" link in the Data column for each table you want to import. After you click "Table", you'll be able to perform transformations on the data.

[jcmayernik]

Thanks again!