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:

image

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:

image

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:

image

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:

image

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

SNAGHTML32d5a902

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