Creating Power BI Report To Number Of Files Uploaded And Modified

Background

 
The organization that I am working for has multiple site collections and within each Site Collection, there are multiple sites. Even some site collections have more than 150 subsites and growing.
 
There was a requirement about how we could check the usage, based on the number of documents uploaded and edited each month. One way of checking in is to use Site Collection Audit report but that is a very time-consuming process.
 
I chose to use SharePoint Search API to generate reports using Power BI. My requirement was to get usage. I used M-Query to get the desired results in a Data Table.
 
Below is the code, 
  1. let  
  2.     // The URL request string must be essentially static in order for the dataset to refresh in Power BI Online.    
  3.     // This means that the entire string must be known based entirely on information in this particular Query before runtime.    
  4.     // The syntax below (concatenation of static strings) is pretty much the limit of what you can get away with.    
  5.     // ADJUST THE urlRequestString HERE TO POINT TO YOUR TENANT OR SITE    
  6.     urlRequestString = "<SiteCollectionURL>/_api/search/query?querytext='(IsDocument:True AND Path:<SiteCollectionURL>*)'&startrow=0&trimduplicates=false&rowlimit=500&selectproperties='Author,Title,Path,Created,Modified,ModifiedBy,SiteTitle,ContentType,SPWebUrl'",  
  7.     // The following two lines will get you a "You can't schedule refresh for this dataset because one or more sources currently don't support refresh." error in PowerBI online.    
  8.     //urlRequestString = "<SiteCollectionURL>/_api/search/query" & queryParams,    
  9.     //urlRequestString = queryParams,    
  10.     // Initial Web request to only get TotalRows    
  11.     Source = Json.Document(Web.Contents(urlRequestString,  
  12.         [  
  13.             Headers = [#"Accept" = "application/json"]  
  14.         ])),  
  15.     // RelevantResults will be the total number of rows we expect to download    
  16.     PrimaryQueryResult = Source[PrimaryQueryResult],  
  17.     RelevantResults = PrimaryQueryResult[RelevantResults][TotalRows],  
  18.     // Generate a table of values for startrow    
  19.     Offset = List.Generate(() => 0, each _ <= Number.RoundTowardZero(RelevantResults / 100) * 100, each _ + 500),  
  20.     //Convert the list to a table    
  21.     OffsetToTable = Table.FromList(Offset, Splitter.SplitByNothing(), nullnull, ExtraValues.Error),  
  22.     Terms = Table.RenameColumns(OffsetToTable, {  
  23.         {  
  24.             "Column1",  
  25.             "Term"  
  26.         }  
  27.     }),  
  28.     #"Removed Top Rows" = Table.Skip(Terms, 0),  
  29.     Termset = Table.TransformColumnTypes(#"Removed Top Rows", {  
  30.         {  
  31.             "Term",  
  32.             type text  
  33.         }  
  34.     }),  
  35.     // Define the Web request and processing function that will be called multiple times to get the rows we want 500 at a time (the max number of rows SharePoint will return).    
  36.     // Note that the only variable part of the request, the startrow, is sent in as a Query parameter, not concatenated to the static urlRequestString.    
  37.     // This is how we workaround the static urlRequestString requirement.    
  38.     // (I have not been able to make Query parameters work properly with OData.Feed(). When I try, I get weird errors where the rows for every page of results are exactly the same as for the first page of results.)    
  39.     SearchSuccessful = (Term) =>  
  40.     let Source = Json.Document(Web.Contents(urlRequestString,  
  41.             [  
  42.                 Query = [#"startrow" = Term],  
  43.                 Headers = [#"Accept" = "application/json"]  
  44.             ])),  
  45.         Rows = Source[PrimaryQueryResult][RelevantResults][Table][Rows],  
  46.         AllRows = List.Transform(Rows, each _[Cells]),  
  47.         RowsToTables = List.Transform(AllRows, each List.Transform(_, each Record.ToTable(_))),  
  48.         SkelToList = List.Transform(RowsToTables, each Table.FromList(_, Splitter.SplitByNothing(), nullnull, ExtraValues.Error)),  
  49.         CleanRows = List.Transform(SkelToList, each List.Transform(_[Column1], each Table.PromoteHeaders(Table.RemoveLastN(Table.RemoveColumns(_, {  
  50.             "Name"  
  51.         }), 1)))),  
  52.         TransposeTable = Table.FromRows(List.Transform(CleanRows, each List.Transform(_, each Record.FieldValues(_ {  
  53.             0  
  54.         }) {  
  55.             0  
  56.         }))),  
  57.         ColumnRenames = List.Transform(CleanRows {  
  58.             0  
  59.         }, each {  
  60.             "Column" & Text.From(List.PositionOf(CleanRows {  
  61.                 0  
  62.             }, _) + 1), Table.ColumnNames(_) {  
  63.                 0  
  64.             }  
  65.         }),  
  66.         RenamedTable = Table.RenameColumns(TransposeTable, ColumnRenames) in RenamedTable,  
  67.         // Call SearchSuccessful multiple times to get all the rows    
  68.         Output = Table.AddColumn(Termset, "c", each SearchSuccessful([Term])),  
  69.         // Output will be a table of tables. The following transforms Output into a single table of all the rows.    
  70.         // If you change the "selectproperties=", you can either edit the code below by hand, or regenerate it by selecting Output in the Applied Steps righthand pane and doing and "Expand Column" on the "c" column    
  71.         #"SPResultsTable" = Table.ExpandTableColumn(Output, "c", {  
  72.             "Author",  
  73.             "Title",  
  74.             "Path",  
  75.             "Created",  
  76.             "Modified",  
  77.             "ModifiedBy",  
  78.             "SiteTitle",  
  79.             "ContentType",  
  80.             "SPWebUrl"  
  81.         }, {  
  82.             "Author",  
  83.             "Title",  
  84.             "Path",  
  85.             "Created",  
  86.             "Modified",  
  87.             "ModifiedBy",  
  88.             "SiteTitle",  
  89.             "ContentType",  
  90.             "SPWebUrl"  
  91.         }),  
  92.         #"Changed Type of Created" = Table.TransformColumnTypes(#"SPResultsTable", {  
  93.             {  
  94.                 "Created",  
  95.                 type datetime  
  96.             },  
  97.             {  
  98.                 "Modified",  
  99.                 type datetime  
  100.             }  
  101.         }),  
  102.         AddColumnCreatedYearandMonth = Table.AddColumn(#"Changed Type of Created""MonthandYear", each Date.ToText(DateTime.Date([Created]), "yyyy-MM-MMMM")) in AddColumnCreatedYearandMonth   
In this article, we are going to learn how to get data through Rest Service, get data in Data table, how to use Graph to show data, and how to use slicers to filter data.
 
How to get the data through Rest Service 
 
Creating Power BI Report To Number Of Files Uploaded And Modified
 
Once code is pasted in the advanced editor, click apply. 
 
How to get the data in DataTable
 
Change the query name relevant to your data, once you get the data in your data table then you can create a report out of it.
 
You can also use M-query to transform your data.
 
Creating Power BI Report To Number Of Files Uploaded And Modified
 
How to get the data in Bar Graph
 
Follow the steps shown below to set up a graph for your data.
 
Creating Power BI Report To Number Of Files Uploaded And Modified
 
How to add Slicers for filters
 
Add slicers to your report to filter data, you can choose multiple slicers. In this example, I added two slicers, which are Created date and Site Title.
 
Creating Power BI Report To Number Of Files Uploaded And Modified
 
Entire Report with Slicers 
 
Below is the entire report with slicers.
 
Creating Power BI Report To Number Of Files Uploaded And Modified
 
We can also create slicers for Modified Date or any other column, say for example - Content-type.
 
Note
We can publish the Power BI report to Power BI Online, but it will not refresh automatically, it needs to be refreshed manually.