Power BI Tips: Import SharePoint Files or Folder Contents Easily with Power Query

Key insights

• Power Query in Power BI allows users to connect, transform, and combine data from various sources, including SharePoint, making it easier to analyze and report on files stored online.
  
• The two main functions for accessing SharePoint data are SharePoint.Files, which retrieves all files (including those in subfolders), and SharePoint.Contents, which lists both files and folders only in the root directory.
  
• If you need a list of just folders (not files) from a SharePoint or local folder, use Folder.Contents or SharePoint.Contents. Filter the results by expanding the "Attributes" column and selecting rows where the "Directory" attribute is true.
  
• This approach is helpful when folder names contain important information or when you want to identify empty folders that don’t have any files inside them.
  
• You can create a custom Power Query function to automate getting a filtered list of folders from a given path, making repeated tasks faster and more reliable.
  
• This method streamlines your workflow by ensuring that your Power BI reports always reflect current data from SharePoint or local folders without needing manual updates.

Introduction: Unlocking SharePoint Data in Power BI with Power Query

Power BI has become a cornerstone of data analysis and visualization for organizations worldwide, especially those deeply invested in the Microsoft ecosystem. One of the most sought-after capabilities is the seamless integration of SharePoint files and folders into Power BI reports. In a recent you_tube_video, Reza Rad of RADACAD, a Microsoft MVP and recognized expert in data platform technologies, delves into the nuances of accessing SharePoint data using Power Query. His tutorial not only clarifies the differences between two essential Power Query functions—SharePoint.Files and SharePoint.Contents—but also guides viewers through practical scenarios where each approach shines. As organizations grow, so does the complexity of their data storage. SharePoint often serves as a central repository, housing a wide array of files and folders that teams need to analyze and report on. However, efficiently extracting relevant information from SharePoint without cumbersome manual intervention can be challenging. Rad’s walkthrough provides a timely exploration of how Power Query streamlines this process, making it accessible even to users who may not be seasoned programmers.

Understanding SharePoint.Files vs. SharePoint.Contents in Power Query

To begin, Rad explains that Power Query offers two primary functions to retrieve data from SharePoint: SharePoint.Files and SharePoint.Contents. While both are designed to connect Power BI to SharePoint repositories, they serve distinct purposes and present unique advantages and limitations. SharePoint.Files is typically used when the goal is to access all files across a SharePoint site, including those nested within subfolders. This function is particularly effective for scenarios where you need to combine or analyze data from multiple files sharing a similar structure. For instance, a team might store monthly sales reports in various subfolders; SharePoint.Files allows users to aggregate all these reports into a single dataset for unified analysis. However, this comprehensive approach can sometimes introduce challenges, such as increased load times or difficulties filtering out irrelevant files. In contrast, SharePoint.Contents offers a more granular view by listing all items—both files and folders—located in the root of a specified SharePoint directory. This function proves invaluable when users want to explore the organization of folders themselves or when folder names carry significant metadata, such as project names or event identifiers. However, a notable tradeoff is that SharePoint.Contents does not traverse subfolders, requiring additional steps if deeper directory structures must be analyzed. Rad’s explanation emphasizes that choosing between these functions depends on the specific use case. If extracting and consolidating data from a large number of files is the priority, SharePoint.Files is generally preferred. Conversely, when folder-level context or organization is crucial, SharePoint.Contents becomes the tool of choice.

Extracting Only Folder Information: Practical Techniques and Tradeoffs

A recurring challenge for Power BI users is extracting a list of folders from SharePoint without including files. While it may seem straightforward, the default behavior of many Power Query connectors is to focus on files, potentially overlooking valuable metadata stored in folder names. Rad illustrates that folder names themselves can provide essential context, especially when directories are organized by project, client, or event. The standard “Get Data from Folder” option in Power BI retrieves all files within a given directory and its subfolders. To isolate folder names, one could use the “Folder Path” column and perform distinct operations, but this method is cumbersome and risks omitting empty folders. Rad introduces a more streamlined approach by modifying the default Power Query function from Folder.Files to Folder.Contents. This subtle change instructs Power Query to return all items—files and folders—within the root directory. Yet, another challenge arises: distinguishing between files and folders. While file extensions can sometimes indicate file types, not all files have extensions. To address this, Rad demonstrates how to expand the “Attributes” column in the Power Query editor to reveal a “Directory” flag. Filtering for rows where the “Directory” attribute is true effectively isolates folders, ensuring no folder is missed—even those without files. This technique highlights an important tradeoff: while Folder.Contents provides a cleaner list of folders, it does not search recursively. Users needing a comprehensive inventory of all folders, including those nested within subfolders, may need to implement additional logic or custom functions.

Applying the Approach to SharePoint and OneDrive

Rad extends his methodology to cloud-based repositories such as SharePoint and OneDrive. Here, the equivalent function to Folder.Contents is SharePoint.Contents. This function operates similarly, allowing users to enumerate all items in the root of a SharePoint directory, and then filter for folders using the same attribute-based technique described earlier. This approach is particularly useful for organizations that use SharePoint as a central hub for project management, documentation, or event tracking. By leveraging SharePoint.Contents, teams can quickly compile lists of active projects or event folders, even if some directories are empty or still in development. The ability to extract folder-level metadata without downloading or opening individual files streamlines administrative and reporting workflows. However, the challenges of scale and security must also be considered. Large SharePoint sites with complex permission structures may require careful configuration to ensure that Power BI users have appropriate access. Additionally, performance considerations come into play when querying sites with thousands of directories, necessitating thoughtful filtering and data transformation within Power Query.

Automating Folder Extraction: The Power of Custom Functions

Recognizing that many organizations perform folder extraction tasks repeatedly, Rad recommends encapsulating the process in a reusable Power Query custom function. He provides a sample function that takes a directory path as input, applies the Folder.Contents logic, expands the necessary attributes, filters for directories, and sorts the results by name. This automation not only saves time but also reduces the risk of errors associated with manually repeating the same steps. By making the function modular, organizations can adapt it to different SharePoint sites or local folders as needed. Furthermore, custom functions support advanced scenarios, such as recursive folder searches or integration with other data sources. Nevertheless, developing and maintaining custom functions requires a certain level of expertise in Power Query’s M language. Organizations must balance the benefits of automation with the need for maintainability and knowledge transfer. Proper documentation and training are essential to ensure that these solutions remain accessible as team members change or business needs evolve.

Conclusion: Best Practices and Final Insights

Reza Rad’s in-depth video and supporting blog post offer a comprehensive guide to extracting folder and file metadata from SharePoint into Power BI using Power Query. By carefully comparing SharePoint.Files and SharePoint.Contents, he empowers users to select the most effective approach for their unique requirements. Key takeaways include the importance of understanding Power Query’s behavior when handling files versus folders, the value of folder metadata in organizational reporting, and the efficiency gains from automating repetitive tasks with custom functions. However, users should remain mindful of the tradeoffs involved—such as the depth of folder searches, performance implications, and the need for appropriate permissions. As organizations continue to rely on cloud-based storage and data-driven decision-making, mastering these Power Query techniques will be increasingly valuable. Rad’s guidance ensures that both technical and non-technical users can harness the full potential of Power BI and SharePoint integration, driving more informed business outcomes across the enterprise.

Keywords

SharePoint Power BI integration Power Query SharePoint files in Power BI Power BI folder contents SharePoint data import Power Query tutorial Power BI SharePoint connection