If you’re reading this because you’re currently struggling (like I once did) to find a specific document in SharePoint using the Power Query Editor, I’ll cut right to the chase.
In your query, replace “= SharePoint.Files” with “= SharePoint.Contents”. By doing this, instead of facing a flood of every file from your SharePoint site, you’ll be presented with a navigable table displaying folders and files at each level of your SharePoint site.
This table can be navigated by clicking on ‘Table’ in the Content column.
Now, let me delve deeper into what’s happening here.
By default, when you connect Power BI to a SharePoint folder, you’re provided with a list of files within that folder.
If your SharePoint folder houses thousands of documents, locating the exact document you need can feel like searching for a needle in a haystack.
Power Query loads only a certain number of rows initially, often for performance reasons. So, even though you can apply various filtering rules to find your file, it still depends on the luck of the draw. If your file isn’t among the initially loaded data, it might not appear, even after applying the filters.
Create a new SharePoint Site
One potential solution is creating a new SharePoint site to host your files. This approach narrows down the number of files that need to be loaded to those you’re primarily interested in.
However, this might not be feasible for everyone, and the hassle of creating a new site for new reports might simply not be worth it.
SharePoint.Contents
The solution I proposed initially involves changing the query from “= SharePoint.Files” to “= SharePoint.Contents”. This modification transforms the view from a list of all files on the site to a structured, navigable table of folders and files.
In this view, by clicking on ‘Table’, you can navigate through the different levels of the SharePoint site more intuitively, facilitating a more logical file search process.
The ‘Navigation’ step, created as you navigate through the tables, provides even greater convenience when it comes to navigating your folders. By clicking on the gear icon, you gain access to a directory view of your folders. This perspective allows you to visualize the structure of your folders, making it easier for you to locate your desired file.
It’s surprising that these highly useful features are somewhat hidden and difficult to find in Power BI. I hope these tips have made your experience a bit easier and more efficient.