Get ALL SharePoint Sites & Subsites Metrics with Power BI

SharePoint has a wealth of data available on sites, lists, files, and activities in the OData feeds. This is particularly useful to get reporting on subsites, which has been historically hard to get. Here’s how to access this data with Power BI. In this example, we get a list of all of our SharePoint sites, their recent/lifetime views, and last activity date.

Here’s the text for the Power Query site query mentioned in the video:

/_api/search/query?querytext='(contentclass:STS_Site)(contentclass:STS_Web)'&trimduplicates=false&rowlimit=50000

This technique is courtesy of Jordan Murphy, who reached out suggesting it as a video topic. He’s done some awesome work creating queries for all sorts of things in relation to this:
LinkedIn: jordanmurphysphr

0:00 Intro
2:39 Overview of what's in the API
7:00 Creating your queries
17:00 Visualizing

• #science_technology
• #tutorial