Need Help with Microsoft Graph API Pagination in Power Query

[Zack92]

Hi everyone,

I need some help with Microsoft Graph API pagination in Power Query. I'm pulling data for each user, but it doesn’t seem like I’m getting all the events. I think the pagination isn’t working correctly. Without any filters, it's only pulling 30 events, but this user has more than 30 events just for this month.

I’ve tried using ChatGPT and other online resources to figure out how to handle pagination, but I’m still having trouble. Can anyone help me figure out the right way to pull all the data? My Users table has a bunch of emails for users.

below is my code, Does anyone have any ideas on how to properly pull all the data?Or why its only pull 30. 

let
    UsersTable = Users, 
    userEmails = Table.Column(UsersTable, "Email"),

    startDate = Date.ToText(Date.AddMonths(Date.From(DateTime.LocalNow()), -1), "yyyy-MM-dd") & "T00:00:00Z",
    endDate = Date.ToText(Date.AddMonths(Date.From(DateTime.LocalNow()), 6), "yyyy-MM-dd") & "T00:00:00Z",
    
    BaseUrl = "https://graph.microsoft.com",

    GetUserEvents = (userEmail as text) =>
        let
            RelativePath = "v1.0/users/" & userEmail & "/calendar/events",
                        
            GetPage = (relativePath) =>
                let
                    Source = Json.Document(Web.Contents(BaseUrl, [
                        RelativePath = relativePath,
                        Headers = [
                            Authorization = "Bearer " & Token,
                            #"Content-Type" = "application/json"
                        ]
                    ])),
                    currentEvents = Source[value],
                    nextLink = try Source[#"@odata.nextLink"] otherwise null
                in
                    [Events = currentEvents, NextLink = nextLink],

            GetAllPages = (relativePath) =>
                let
                    FirstPage = GetPage(relativePath),
                    AllPages = List.Generate(
                        () => FirstPage,
                        each [NextLink] <> null,
                        each GetPage(Text.AfterDelimiter([NextLink], "https://graph.microsoft.com/")),
                        each [Events]
                    ),
                    AllEvents = List.Combine(AllPages)
                in
                    AllEvents,

            Events = GetAllPages(RelativePath),
            #"Converted to Table" = Table.FromList(Events, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
            #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {
                "@odata.etag", "id", "createdDateTime", "lastModifiedDateTime", "changeKey", "categories", 
                "transactionId", "originalStartTimeZone", "originalEndTimeZone", "iCalUId", "uid", 
                "reminderMinutesBeforeStart", "isReminderOn", "hasAttachments", "subject", "bodyPreview", 
                "importance", "sensitivity", "isAllDay", "isCancelled", "isOrganizer", "responseRequested", 
                "seriesMasterId", "showAs", "type", "webLink", "onlineMeetingUrl", "isOnlineMeeting", 
                "onlineMeetingProvider", "allowNewTimeProposals", "occurrenceId", "isDraft", "hideAttendees", 
                "responseStatus", "body", "start", "end", "location", "locations", "recurrence", 
                "attendees", "organizer", "onlineMeeting", "calendar@odata.associationLink", 
                "calendar@odata.navigationLink"
            })
        in
            #"Expanded Column1",

    AllUserEvents = List.Transform(userEmails, each GetUserEvents(_)),
    CombinedEvents = Table.Combine(AllUserEvents)
in
    CombinedEvents

Users Table

Output of API

Thanks in advance!

[v-nmadadi-msft]

Hi @Zack92 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-nmadadi-msft]

Hi @Zack92 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-nmadadi-msft]

Hi @Zack92  ,

We have not heard back from you if the answer has met your requirement kindly  Accept it as the solution  to help out other community members or get back to us with any other questions.
Thank you

[v-nmadadi-msft · Accepted solution]

Hi @Zack92   ,

Thank you for reaching out to Microsoft community support,

It seems like pagination is  not handled properly.
The API response includes an @odata.nextLink property when there are more items to fetch beyond the initial page.
If your code does not correctly use @odata.nextLink to retrieve subsequent pages, you'll only get the first 30 results.
In your current code, the logic for fetching subsequent pages via GetALLpages might not be working as intended, resulting in only the first page being retrieved.

This documentation may help you in your error

Paging Microsoft Graph data in your app - Microsoft Graph | Microsoft Learn

If the issue persists, consider using Power Automate (formerly Flow) to retrieve data with Graph API pagination and save it to a file or data source that Power Query can access. Power Automate handles pagination more seamlessly.

Please consider Accepting the solution if the provided answer meets your requirement so as to help other users or get back to us if you have further queries.

Thanks and Regards