Count documents and size in SharePoint libraries using Power BI

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.

Windows 10 Touch Pad Gestures

Windows 10 is installed on most new Laptops, and here are some tips to effectively take advantage of the touch pad. Meet the cool Touch Pad gestures –

 

Select an item: Tap on the touchpad.

Scroll: Place two fingers on the touchpad and slide horizontally or vertically.

Zoom in or out: Place two fingers on the touchpad and pinch in or stretch out.

Right-click: Tap the touchpad with two fingers, or press in the lower-right corner.

Show Open windows: Place three fingers on the touchpad and swipe them away from you.

Show the desktop: Place three fingers on the touchpad and swipe them towards yourself.

Switch between open windows: Place three fingers on the touchpad and swipe right or left.

Dynamic infographics with Microsoft Excel data on a real map

Have you wished you could plot numbers in a table on a real map; and not use map picture. Also, this infographic thus created should dynamically refresh based on data. There is a very easy way to do this, even for a complete non-techie without having to fiddle with geo-coding, latitudes and longitudes.

Meet the “Swiss Knife” Calculator of Windows

Microsoft Windows Tip: If you think the default calculator that came with Windows (7, 8, 8.1) was just a normal calculator you are wrong. Don’t launch Excel yet! This calculator is a Swiss knife of calculators.

Click “View” in the menu of the Calculator and you will see the array of calculations this app can do –

Preparing single presentation for multiple audiences

Microsoft PowerPoint Tip: You want to prepare a corporate presentation but it needs various versions and edits as it will delivered to different audiences. For a company, audiences can be – customers, suppliers, partners, investors, press, regulators, job seekers, employees, new hires, etc.

Here is the problem –

• About 60 – 70% of the slides will be common in a company presentation.
• Yet you will make several “copies” with 30 – 40% customized for different audiences.
• When changing updated content/figures on slides, or adding new common slides, you will need to make the same changes across all presentations.