Excel Power Query Hidden Sheet

Lastly rename the query in the query settings properties with something useful as this will be the table name in excel power pivot.

Excel power query hidden sheet. If you are in the same workbook you need to know the name of the table you want to access. But to answer your question. After linking to an external excel file there are three options of data extracting available. In 2016 it was renamed to get transform and appears in the data tab without the need to install any add in.

As a workaround in the query editor you can go back to the source step and drill drill down the hidden sheet. I would then use power query to gather the data from the different files and so forth. Excel sheet as a source to power query and power bi. When you import data from an excel workbook to the power query or power bi from entire sheet be careful there is a pitfall.

Per my test the hidden sheets are not shown in the navigator ui. View solution in original post. Power query is available as an add in to download and install for excel 2010 and 2013 and will appear as a new tab in the ribbon labelled power query. Yes it is possible.

A pitfall of usedrange. From another workbook or power bi you can select something in the excel sheet go back to the source step and there you can select also content on very hidden sheets. If you want to access from outside the workbook e g. Now you re ready to close load.