After the post “Get Sheet Names using VBA Excel for mac & windows” I started getting queries is it possible to extract the Sheet Name of the Active Workbook or any workbook using Power Query?
Well, Power Query has a solution to all our problems & it is possible by using Power Query.
Steps to follow
In order to get sheet names we must first save our Excel Workbook.
From the Ribbon Tab Select > Data > Get Data > From File > From Workbook
Select the Excel Workbook
Power Query Navigator will open.
Select the yellow Folder Icon as shown in the image which will select all the worksheets in that workbook.
Click on Transform Data button
Excel Workbook is loaded into the Power Query Editor and we can see all the sheet names in from that workbook in the Column [Name].
Note: If need to change the file path then come to Power Query Editor click on to the Source step setting icon to change, see this blog article[coming soon] make the file path dynamic in Power Query
Select the column [Name] > Home > Remove Columns > Remove Other Columns
This action will remove all the other columns and keep only the selected column.
Shortcut Tip: Another way to deal with this just to select the column [Name] > Right Click > Remove Other Columns.
We only have the column with sheet names in the Power Query editor and let rename the column name by double-clicking the column header to [Sheet Name].
Let’s load these sheet names into in our workbook
Home > Close & Load To..
Select the Import Data options
- Table
- New worksheet
- Click OK
All sheet names for are loaded in the workbook
Lets add some new sheet name and rename the exiting default sheet name “Sheet1” to “Sheet Names”
A very important thing to note here that once we have added the sheet we must SAVE our workbook before refreshing the query. If we accidentally forget to SAVE and refresh, then we will not get the new worksheet name.
To refresh the keep the cell in Excel Tables where all the sheet names are listed Right Click > Refresh
Upon refresh, we get our new sheet names along with that we even got some other name. Let go back to our Power Query Editor and understanding what went wrong that we are getting such an unknown name in our sheet names list.
Excel Ribbon > Data > Queries & Connections > Select Query > Double Click
Query Editor will be launched and select the Source step
After clicking the Source step we are back to the beginning of the query and looking at the column[Kind] we understand that Sheet, Excel Table & Name Ranges are also part of the workbook which gets pulled in the Power Query.
While getting the data we didn’t pull any Excel Table or Name Ranges, then how are they appearing here?
So, when we load any query into Excel Workbook it gets to load as Excel Table.
This is super amazing in-fact we can even get the Excel Table Name if we have any in our workbook.
However, to avoid this we need to make our query future proof to make sure we are getting only the sheet when new sheets are added. We can do that just by adding a FILTER step after Source step
Filter column[Kind] = Sheet and if you wish to exclude the “Sheet Name” then we select column[Name] filter out the from the list.
Also if we like to sort in ascending order (A-Z) then we can sort the same.
Click Close & Load
This is a dynamic solution & future when we add any new sheet we just need to SAVE the workbook and then click refresh.
Can we run this query on Excel for mac ?
Yes, we can run this query on Excel for Mac. Currently, the Power Query for mac is in Phase-1 for details please see this video.
The Power Query Editor yet to come for mac, but query what we have built on the PC are possible to run on mac.
Open the Excel workbook on Excel for mac
Click on Enable Content
From the Ribbon Select > Data > Connections > Workbook Connection > Change File Path > Select the File > Get Data > Close > Save Workbook > Refresh
Video
Subscribe to YouTube Channel for Exciting Tips & Tricks On Office Applications
Coming Soon ….
Was the information helpful in this blog?
- If yes, hit that share button and show the excitement to the world.
- Subscribe to our free posts.
If no, please let us know what to improve.
Having trouble with any Office Apps. Feel free to ask and answer queries at our forums section.