Filtering query for PowerBi Odata from Graph, for SharePoint Online Sites

[Blue407]

Following numerous tutorials online, I have successfully configured a query in PowerBi to connect to Microsoft Graph and pull down a list of SharePoint Online Sites (and subsites). It uses a Azure app to provide the relevant permissions to get the data, with a shared seceret for authentication.

Here's the advanced query:

let 
    resource="https://graph.microsoft.com",
    tokenResponse = Json.Document(Web.Contents("https://login.windows.net/",
    [
        RelativePath = #"Tenant ID" & "/oauth2/token",
        Content = Text.ToBinary(Uri.BuildQueryString(
            [
                client_id = #"Azure Application ID",
                resource = resource,
                grant_type = "client_credentials",
                client_secret = #"Azure Application Client Secret"
            ]
        )),
        Headers = [Accept = "application/json"], ManualStatusHandling = {400}
    ])),
    access_token = tokenResponse[access_token],
    Source = OData.Feed("https://graph.microsoft.com/beta/sites", [ Authorization = "Bearer " & access_token ], [ ExcludedFromCacheKey = {"Authorization"}, ODataVersion = 4, Implementation = "2.0" ]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type)
in
    #"Added Index"

Next I want to reduce the amount of data being imported, to speed things up. No point importing all of it and then filtering it, its going to be slow. (Something like 70% of the sites are going to be personal OneDrive sites)

The most obvious filter to start with is ignoring OneDrive sites by checking 'isPersonalSite' = FALSE. No matter syntax I try, it does not work.

Alternatively, looking in the 'id' for only <domainname>.sharepoint.com. ignoring all those that are <domainname>-my.sharepoint.com.

Any suggestions on how to get one of these working with Sites? (Ideally the 'isPersonalSite' one)

Thanks in advanced for ideas and suggestions 🙂

[OwenAuger · Accepted solution]

Hi @Blue407 

After a bit of Googling and tinkering on my own tenant, the below query seems to work.

The changes I made were:

1. Change the serviceUri (1st argument of OData.Feed) to
   "https://graph.microsoft.com/beta/sites/getAllSites"
2. Add a Query record within the options record (3rd argument of Odata.Feed) equal to
   Query = [filter = "isPersonalSite eq false"]

let
  resource = "https://graph.microsoft.com",
  tokenResponse = Json.Document(
    Web.Contents(
      "https://login.windows.net/",
      [
        RelativePath = #"Tenant ID" & "/oauth2/token",
        Content = Text.ToBinary(
          Uri.BuildQueryString(
            [
              client_id     = #"Azure Application ID",
              resource      = resource,
              grant_type    = "client_credentials",
              client_secret = #"Azure Application Client Secret"
            ]
          )
        ),
        Headers = [Accept = "application/json"],
        ManualStatusHandling = {400}
      ]
    )
  ),
  access_token = tokenResponse[access_token],
  Source = OData.Feed(
    "https://graph.microsoft.com/beta/sites/getAllSites",
    [Authorization = "Bearer " & access_token],
    [
      ExcludedFromCacheKey = {"Authorization"},
      ODataVersion         = 4,
      Implementation       = "2.0",
      Query                = [filter = "isPersonalSite eq false"]
    ]
  ),
  #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type)
in
  #"Added Index"

Notes:

• Documentation for sites/getAllSites
  
• You may receive a property @odata.nextLink in the response, so may have update the query to iterate to update to iterate through pages.

Does this work for you?