Using Graph API in Power BI and coping with pagination

[EBWorkingItOut]

Hi 

We've been using an Azure Permissioned app to utilise Graph API within Power bi to track and monitor user login activity, we've been able to return a CSV file with the first 200 records using the script below, and from the different documentation there seems to be pagination being used for the Graph API which would explain this limit as expecting closer to 8,000 results, however every time i attempt to adjust the below to pull in a JSON format which I'm assuming would contain the link to the next set of data in the list power bi throws up errors.

Can anyone advise on the best way forward either modifying the approach below or a different method.

let
token_uri = "https://login.windows.net/" & #"Azure AD Tenant ID" & "/oauth2/token",
resource="https://graph.microsoft.com",
tokenResponse = Json.Document(Web.Contents(token_uri,
[
Content = Text.ToBinary(Uri.BuildQueryString(
[
client_id = #"Azure Application Client 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/v1.0/reports/getM365AppUserDetail(period='D180')?$format=text/csv", [ Authorization = "Bearer " & access_token ], [ExcludedFromCacheKey={"Authorization"}, ODataVersion=4, Implementation="2.0"]),
#"Imported CSV" = Csv.Document(Source,[Delimiter=",", Columns=39, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Report Refresh Date", type date}, {"User Principal Name", type text}, {"Last Activation Date", type date}, {"Last Activity Date", type date}, {"Report Period", Int64.Type}, {"Windows", type text}, {"Mac", type text}, {"Mobile", type text}, {"Web", type text}, {"Outlook", type text}, {"Word", type text}, {"Excel", type text}, {"PowerPoint", type text}, {"OneNote", type text}, {"Teams", type text}, {"Outlook (Windows)", type text}, {"Word (Windows)", type text}, {"Excel (Windows)", type text}, {"PowerPoint (Windows)", type text}, {"OneNote (Windows)", type text}, {"Teams (Windows)", type text}, {"Outlook (Mac)", type text}, {"Word (Mac)", type text}, {"Excel (Mac)", type text}, {"PowerPoint (Mac)", type text}, {"OneNote (Mac)", type text}, {"Teams (Mac)", type text}, {"Outlook (Mobile)", type text}, {"Word (Mobile)", type text}, {"Excel (Mobile)", type text}, {"PowerPoint (Mobile)", type text}, {"OneNote (Mobile)", type text}, {"Teams (Mobile)", type text}, {"Outlook (Web)", type text}, {"Word (Web)", type text}, {"Excel (Web)", type text}, {"PowerPoint (Web)", type text}, {"OneNote (Web)", type text}, {"Teams (Web)", type text}})
in
#"Changed Type"

The error returned when format is changed to application/json is as per the below

DataSource.Error: Microsoft.Mashup.Engine1.Library.Resources.HttpResource: Request failed: 
OData Version: 4, Error: The remote server returned an error: (400) Bad Request. ({"Message":"JSON format is not supported."})
OData Version: 3, Error: The remote server returned an error: (400) Bad Request. ({"Message":"JSON format is not supported."})
Details:
    DataSourceKind=OData
    DataSourcePath=https://graph.microsoft.com/v1.0/reports/getM365AppUserDetail(period='D180')

 Thanks in anticipation

[aegalan]

Hi! what type of connection you do in power bi to make this to work? im using the same code to get the access token and i receive this error:

DataSource.Error:Web.Contents with the Content option is only supported when connecting anonymously.

But when i try to use anonymously connection, there is another error. It seem like the connection must be windows authentication?

Thanks for your advice,

[v-junyant-msft · Accepted solution]

Hi @EBWorkingItOut ,

It seems that the error you're encountering is due to the Graph API endpoint not supporting JSON format for the specific report you're trying to access. The Graph API has different endpoints, and not all of them support JSON format for all types of reports.

However, if you're working with an endpoint that does support JSON, you need to remove the ?$format=text/csv from your URL and ensure your headers are set to accept JSON. In this case, since you're getting an error that "JSON format is not supported," it's likely that the endpoint doesn't support JSON for the report you're requesting.
So please verify if the endpoint supports JSON format for the data you're trying to retrieve.
If it does, adjust your Power Query to remove ?$format=text/csv and ensure your headers are set to accept JSON.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

which I'm assuming would contain the link to the next set of data in the list 

no, that's not part of the payload.