Rest API - Pagination

[NargisseB]

Hello,
I'm trying to extract the users of an azure group, I use first a function ''GetToken'' to get the token, then I use this function to get the users : 

let
    Source = (GroupID as text)=>
let    
    token= GetToken() ,
    Source = Json.Document(Web.Contents("https://graph.microsoft.com",
     [
         RelativePath = "v1.0/groups/" & GroupID & "/members?$top=500",
         Headers=[Authorization="Bearer " &  token]
     ])),
    #"Converted to Table" = Record.ToTable(Source),
    #"Filtered Rows" = Table.SelectRows(#"Converted to Table", each ([Name] <> "@odata.nextLink")),
    Value = #"Filtered Rows"{1}[Value],
    #"Converted to Table1" =  Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" =  Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"@odata.type", "id", "businessPhones", "displayName", "givenName", "jobTitle", "mail", "mobilePhone", "officeLocation", "preferredLanguage", "surname", "userPrincipalName"}, {"Column1.@odata.type", "Column1.id", "Column1.businessPhones", "Column1.displayName", "Column1.givenName", "Column1.jobTitle", "Column1.mail", "Column1.mobilePhone", "Column1.officeLocation", "Column1.preferredLanguage", "Column1.surname", "Column1.userPrincipalName"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Column1",{"Column1.@odata.type", "Column1.businessPhones", "Column1.jobTitle", "Column1.mobilePhone", "Column1.officeLocation", "Column1.preferredLanguage", "Column1.givenName", "Column1.surname", "Column1.userPrincipalName"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1.displayName", "Analytics Full Name"}, {"Column1.mail", "Analytics Login"}, {"Column1.id", "User id"}})
in
    #"Renamed Columns"
in
    Source

 The problem is that the maximum I can get is only 500 users ( Meanwhile I have about 100 000 ) 
I checked some documentation and found out that  with the $top parametre, power bi is supposed to give the rest of the users using the odatalink. I tried to do it with a recursive function : 

let
    GetMembersRecursive = (url as text, token as text) =>
    let
        response = Json.Document(Web.Contents(url, [Headers=[Authorization="Bearer " & token]])),
        members = response[value],
        nextLink = Record.Field(response, "@odata.nextLink", null),
        result = 
            if nextLink <> null then 
                List.Combine({members, GetMembersRecursive(nextLink, token)}) 
            else 
                members
    in
        result
in
    GetMembersRecursive

Then the new code to get the users : 

let
    GetUsersForADGroup = (GroupID as text) =>
    let
        token = GetToken(),
        url = "https://graph.microsoft.com/v1.0/groups/" & GroupID & "/members?$top=500",
        members = GetMembersRecursive(url, token),

        #"Converted to Table" = Table.FromList(members, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "displayName", "mail"}, {"User id", "Analytics Full Name", "Analytics Login"})
    in
        #"Expanded Column1"
in
    GetUsersForADGroup

But unfortuanatly it's not working, I still get errors. Does anyone have another lead? or is there another way to do it without the recursive function ? 

[lbendlin]

 The problem is that the maximum I can get is only 500 users ( Meanwhile I have about 100 000 ) 

This will be agonizingly slow, no matter what you do.  See if you can find an AD extract that already has that information.

Anyway, here is the reference function for API paging.

Handling paging for Power Query connectors - Power Query | Microsoft Learn