Getting SharePoint activity report into Power Bi

[Alex_Roughag]

Hello,

I'm trying to load a SharePoint Site Usage Detail (https://graph.microsoft.com/v1.0/reports/getSharePointSiteUsageDetail(period='D180')) report with OData feed into Power Bi, but I'm getting an error "Access to the resource is forbidden", even though I can easily download https://graph.microsoft.com/V1.0/groups data.

I use a Global Administrator and I tried to add SharePoint Administrator and Reports Reader roles, but the error remains.

Is there any way I can get that report or is there a workaround?

Thank you.

[Alex_Roughag]

Thank you @Anonymous that works.

The only correction is that URL needs to be in quotes in order the query to work.

[Anonymous · Accepted solution]

Hi @Alex_Roughag ,

Have a good day. I made a sample for you.

1\ Registe app in Entra ID and get client id, token link.

2\ Set permission

3\ Add secret and copy it

4\ Create a blank query in BI desktop

let

    //POST Request
    url = "<token link>",
    body = [
        scope = https://graph.microsoft.com/.default,
        client_id = "<client id>",
        client_secret="<secret value>",
        grant_type = "client_credentials"
    ],


     GetJson =
        Json.Document(
            Web.Contents(
                url, [
                    Headers=[
                        Accept="application/json", 
                        #"Content-Type"="application/x-www-form-urlencoded"
                    ], 
                    Content= Text.ToBinary(Uri.BuildQueryString(body))
                ]
            )
        ),
    access_token = GetJson[access_token],
    url2= https://graph.microsoft.com/v1.0,
    Result= Csv.Document(Web.Contents(url2, [RelativePath="/reports/getSharePointSiteUsageDetail(period='D180')", Headers=[Accept="*/*", authorization="Bearer "&access_token]]),[Delimiter=",", Columns=15, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Result, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Report Refresh Date", type date}, {"Site Id", type text}, {"Site URL", type text}, {"Owner Display Name", type text}, {"Is Deleted", type logical}, {"Last Activity Date", type date}, {"File Count", Int64.Type}, {"Active File Count", Int64.Type}, {"Page View Count", Int64.Type}, {"Visited Page Count", Int64.Type}, {"Storage Used (Byte)", Int64.Type}, {"Storage Allocated (Byte)", Int64.Type}, {"Root Web Template", type text}, {"Owner Principal Name", type text}, {"Report Period", Int64.Type}})
in
    #"Changed Type"

5 Upload to service and set certification.

Best Regards,

Wearsky