Count documents and size in SharePoint libraries using Power BI

Posted byYatin PurohitPosted inGeneral, Power BI, SharePoint

Very common scenario requested is to show how many documents are there across document libraries in a SharePoint site and the file sizes. Unfortunately, SharePoint does not offer this out-of-the-box even for a single document library.

Luckily, the new Power BI does offer this capability, albeit not well known. I literally stumbled upon this. Here is what to do (Assuming basic knowledge of Power BI) –

  1. Note or copy the site URL. E.g. tenant-name.sharepoint.com/sites/AccountsDMS
  2. Start Power BI desktop.
  3. Click Get Data and select File (in More..) -> SharePoint Folder. Click Connect.
  4. Paste or type the site URL. Click Ok. Next you will see a list of files. Click Load.
  5. Click on Edit Queries.
  6. Delete the columns Content (actual file) and Date Accessed.
  7. Optionally you can also delete Date Created and Date Modified, if you are not going to do any analysis on those fields. You may retain it if you want to plot files created or modified by day-week-month-etc. on a line chart visualization.
  8. Expand the column Attributes. Select Size Click Ok. Rename Attribure.Size to Size.
  9. Select the column Size. In the Transform group menu, change data type to Whole Number.
  10. Select Folder Path. This is the URL to the document library containing that file.
  11. Click Split columns by Delimiter in Transform group. Type / as the custom delimiter. Click OK.
  12. Delete all Folder Path columns except the Folder Path.n which contains the document library name. The Folder Path column number for the library name may vary based on your site setup. Also, delete the Last Folder Path whose content will typically be blank, since it there is a last /.
  13. Change the Folder Path.n column name to Library.
  14. Save and Load the query.
  15. Select Table visual.
  16. Under Fields, Add Values -> Library, Name, Size.
  17. Summarize Name value to Count of Name. (Context arrow of Name field)
  18. Rename Count of Name to Docs.
  19. Optionally, create a calculated column called KB with formula TableName[Size]*0.001 to save the size in Kilobytes which is more readable. Substitute Size with KB in the table visualization. Rename column Size to Size (KB).
  20. Size and format the table as desired.

Published by Yatin Purohit

Yatin Purohit is a consultant for Business Productivity and IT Optimization with an experience of 23 years. Yatin was employed at Microsoft Corp for 8 years in leadership roles managing large enterprise customers and partners. Yatin conducts workshops for organizations in the area of office productivity. He is also a regular speaker at various IT Forums and associations.

Leave a comment