Navigation Window / Folder in Power Query and Power BI

Motivated by one of the latest videos from the MSFT guys behind Guy in a cube:

where they talk about one of the hidden secrets behind Power Query / Power BI, I wanted to talk about another one of those hidden gems inside Power Query / Power BI that is not completely visible or intuitive for most folks when they start using the tool.

You’ve probably gone through a Navigation window which looks similar to the image below:

and you’ve probably selected one of the elements underneath the folder icon like “Power Query” or “Source Data” from the example above. BUT! what if you want to access ALL of the objects from that data source?

For example, what if we connect to a SharePoint site to get all of the lists from that site. You’ll end up with a navigation window or folder like the following one:

This situation happens with a lot of data sources that give you that “Navigator” or “Navigation” window/folder.

The solution is quite easy and simple yet not so intuitive to the user. All you need to do is right click on the folder icon and click on Edit:

This is something that has been around since the inception of Power Query, but people rarely use it since you tend not to use the right click when you’re in this window. The result of that operation will create a query with basically all of the elements inside that “folder” and it’ll look like this:

and yes! just to reiterate, this works on any data source that shows you that “Navigator” or “Navigation” window/folder.

so there you have it! a hidden gem that you can start using right away