import data from hidden sheet

[yxz152830]

Gurus,
is there a way for power query to load data from hidden sheets in another workbook? thanks!

 

[Misca]

Power Query loads data from hidden sheets just as they were visible sheets. There's no difference whatsoever. The only difference is the user interface:

When you use the Get Data / From File / From Workbook -dialog the dialog only show the visible sheets. However, if you select one of the visible sheets and take a look at the Applied Steps, the Source step is showing all the sheets there are. Just change the Navigation step to get data from the sheet(s) you're interested in.

 

[yxz152830]

hello when i load in the transform interface, there are typically three steps and none shows me all the sheets.
Source step shows the file path and below is navigation step showing the sheet for table extraction.

= Source{[Item="FLT OTP",Kind="Sheet"]}[Data]

Power Query loads data from hidden sheets just as they were visible sheets. There's no difference whatsoever. The only difference is the user interface:

When you use the Get Data / From File / From Workbook -dialog the dialog only show the visible sheets. However, if you select one of the visible sheets and take a look at the Applied Steps, the Source step is showing all the sheets there are. Just change the Navigation step to get data from the sheet(s) you're interested in.

,

 

[Misca]

I turned the Source step into a function:

(MyFilePath as text)=>
let
    Source = Excel.Workbook(File.Contents(MyFilePath), null, true)
in 
Source

If you're loading data from all the files in a folder then MyFilePath = [Folder Path] & [Name] (i.e. full file path + the workbook name).

 

[yxz152830]

I turned the Source step into a function:

(MyFilePath as text)=>
let
    Source = Excel.Workbook(File.Contents(MyFilePath), null, true)
in
Source

If you're loading data from all the files in a folder then MyFilePath = [Folder Path] & [Name] (i.e. full file path + the workbook name).

I was just wondering if during the loading data phase, the power query can show me all the tabs including hidden tabs in an excel workbook. Right now all I'm seeing are the visible tabs. If I wanna use your method I think I need to know the names of different tabs in the first place, which means I have to open one of the workbooks and find out the names myself