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, 

let
    // The URL request string must be essentially static in order for the dataset to refresh in Power BI Online.
    // This means that the entire string must be known based entirely on information in this particular Query before runtime.
    // The syntax below (concatenation of static strings) is pretty much the limit of what you can get away with.
    // ADJUST THE urlRequestString HERE TO POINT TO YOUR TENANT OR SITE
    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'",
    // 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.
    //urlRequestString = "<SiteCollectionURL>/_api/search/query" & queryParams,
    //urlRequestString = queryParams,
    // Initial Web request to only get TotalRows
    Source = Json.Document(Web.Contents(urlRequestString,
        [
            Headers = [#"Accept" = "application/json"]
        ])),
    // RelevantResults will be the total number of rows we expect to download
    PrimaryQueryResult = Source[PrimaryQueryResult],
    RelevantResults = PrimaryQueryResult[RelevantResults][TotalRows],
    // Generate a table of values for startrow
    Offset = List.Generate(() => 0, each _ <= Number.RoundTowardZero(RelevantResults / 100) * 100, each _ + 500),
    //Convert the list to a table
    OffsetToTable = Table.FromList(Offset, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Terms = Table.RenameColumns(OffsetToTable, {
        {
            "Column1",
            "Term"
        }
    }),
    #"Removed Top Rows" = Table.Skip(Terms, 0),
    Termset = Table.TransformColumnTypes(#"Removed Top Rows", {
        {
            "Term",
            type text
        }
    }),
    // 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).
    // Note that the only variable part of the request, the startrow, is sent in as a Query parameter, not concatenated to the static urlRequestString.
    // This is how we workaround the static urlRequestString requirement.
    // (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.)
    SearchSuccessful = (Term) =>
    let Source = Json.Document(Web.Contents(urlRequestString,
            [
                Query = [#"startrow" = Term],
                Headers = [#"Accept" = "application/json"]
            ])),
        Rows = Source[PrimaryQueryResult][RelevantResults][Table][Rows],
        AllRows = List.Transform(Rows, each _[Cells]),
        RowsToTables = List.Transform(AllRows, each List.Transform(_, each Record.ToTable(_))),
        SkelToList = List.Transform(RowsToTables, each Table.FromList(_, Splitter.SplitByNothing(), null, null, ExtraValues.Error)),
        CleanRows = List.Transform(SkelToList, each List.Transform(_[Column1], each Table.PromoteHeaders(Table.RemoveLastN(Table.RemoveColumns(_, {
            "Name"
        }), 1)))),
        TransposeTable = Table.FromRows(List.Transform(CleanRows, each List.Transform(_, each Record.FieldValues(_ {
            0
        }) {
            0
        }))),
        ColumnRenames = List.Transform(CleanRows {
            0
        }, each {
            "Column" & Text.From(List.PositionOf(CleanRows {
                0
            }, _) + 1), Table.ColumnNames(_) {
                0
            }
        }),
        RenamedTable = Table.RenameColumns(TransposeTable, ColumnRenames) in RenamedTable,
        // Call SearchSuccessful multiple times to get all the rows
        Output = Table.AddColumn(Termset, "c", each SearchSuccessful([Term])),
        // Output will be a table of tables. The following transforms Output into a single table of all the rows.
        // 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
        #"SPResultsTable" = Table.ExpandTableColumn(Output, "c", {
            "Author",
            "Title",
            "Path",
            "Created",
            "Modified",
            "ModifiedBy",
            "SiteTitle",
            "ContentType",
            "SPWebUrl"
        }, {
            "Author",
            "Title",
            "Path",
            "Created",
            "Modified",
            "ModifiedBy",
            "SiteTitle",
            "ContentType",
            "SPWebUrl"
        }),
        #"Changed Type of Created" = Table.TransformColumnTypes(#"SPResultsTable", {
            {
                "Created",
                type datetime
            },
            {
                "Modified",
                type datetime
            }
        }),
        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 

 

 

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.

 

 

How to get the data in Bar Graph

 

Follow the steps shown below to set up a graph for your data.

 

 

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.

 

 

Entire Report with Slicers 

 

Below is the entire report with slicers.

 

 

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.