Power Query: Hidden Talents
30 March 2022
Welcome to our Power Query blog. This week, I look at how to find data in hidden sheets.
My fictional salespeople are worried. There is a rumour that someone is in trouble after the latest review. They have found an Excel Workbook, but it seems nothing has been revealed yet:
I can link to this Excel Workbook from a new Excel Workbook:
From the ‘Get Data’ dropdown in the ‘Get & Transform’ section of the Data tab, I can choose ‘From Workbook’. In the browse dialog, I navigate to the correct Excel Workbook:
According to the Navigator dialog, there is only one table of data available and that’s not revealing anything. I decide to ‘Transform Data’ anyway.
The data has been automatically transformed for me in four [4] steps. In reverse order, the ‘Changed Type’ step has picked the data types for the columns, based upon algorithms that look at the data in the columns:
= Table.TransformColumnTypes(#"Promoted Headers",{{"Name", type text}, {"Review Date", type date}, {"Result", type text}})
The previous step, ‘Promoted Headers’ has extracted the column headings from the first row of data:
= Table.PromoteHeaders(Employees_Sheet, [PromoteAllScalars=true])
The second step, Navigation, extracted the Employees sheet from the workbook:
= Source{[Item="Employees", Kind="Sheet"]}[Data]
The most interesting step for me, is the Source step:
= Excel.Workbook(File.Contents("C:\Users\kathr\OneDrive\Documents\SUMPRODUCT\PQ Blog\PQ blog 278 hiding.xlsm"), null, true)
There is the Employees sheet, but what is this? Another sheet: Management Only. I click on the ‘Table’ text in the Data column next to Management Only:
Power Query warns me that the steps to extract the Employees sheet will be replaced. I choose ‘Continue’:
The steps are replaced, and the new data is revealed. It’s not good news for John, and Newbie should be worried! So why couldn’t we see this data when extracting from the Excel Workbook? The answer is on the Source step:
The Hidden column shows that the Management Only sheet was hidden, so it wasn’t shown on the extraction Navigator dialog. This is a useful trick to find hidden data.
Come back next time for more ways to use Power Query!
