I want to extract all members from all there group of my company for that i create the first function to extract all group that i need :
-------------------------------------------------------------------------------------------------------------------------------------------
()=>
let
body = "{""grant_type"":""password"", ""resource"":""https://graph.microsoft.com/"", ""client_id"":""*****************************"", ""username"":""*******************"", ""password"":""**************"",""client_secret"": ""******************************"" }",
parsed_json = Json.Document(body),
buildQueryString = Uri.BuildQueryString(parsed_json),
Data = Json.Document(Web.Contents("https://login.microsoftonline.com/common/oauth2/token/", [Headers=[#"Content-Type"="application/x-www-form-urlencoded"], Content=Text.ToBinary(buildQueryString)])),
access_token = Data[access_token]
in
access_token
-------------------------------------------------------------------------------------------------------------------------------------------
Everything is ok for the groups
After that i use the first function of the Group list to extract the list of corresponding members
-------------------------------------------------------------------------------------------------------------------------------------------
let
Source = (GroupID as text)=>
let
token= GetMSGraphAccessToken() ,
[
RelativePath = "v1.0/groups/" & GroupID & "/members?$count=true",
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
-------------------------------------------------------------------------------------------------------------------------------------------
knowing that the total number of members of one of the groups are 327, the problem of second function don't give me more then 100 members,
-------------------------------------------------------------------------------------------------------------------------------------------
I use another function to make a filter with an alphabetical order for the letter A
let
Source = A("GroupID"),
Navigation = Source{2}[Value],
#"Converti en table" = Table.FromList(Navigation, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Column1 développé" = Table.ExpandRecordColumn(#"Converti en table", "Column1", {"@odata.type", "id", "businessPhones", "displayName", "givenName", "jobTitle", "mail", "mobilePhone", "officeLocation", "preferredLanguage", "surname", "userPrincipalName"}, {"@odata.type", "id", "businessPhones", "displayName", "givenName", "jobTitle", "mail", "mobilePhone", "officeLocation", "preferredLanguage", "surname", "userPrincipalName"}),
#"Lignes triées" = Table.Sort(#"Column1 développé", {{"displayName", Order.Ascending}}),
#"businessPhones développé" = Table.ExpandListColumn(#"Lignes triées", "businessPhones"),
#"Lignes filtrées" = Table.SelectRows(#"businessPhones développé", each Text.StartsWith([userPrincipalName], "a") or Text.StartsWith([userPrincipalName], "A")
#"Colonnes supprimées" = Table.RemoveColumns(#"Lignes filtrées", {"businessPhones", "@odata.type", "preferredLanguage", "officeLocation", "mobilePhone"}),
#"Transformer les colonnes" = Table.TransformColumnTypes(#"Colonnes supprimées", {{"id", type text}, {"displayName", type text}, {"givenName", type text}, {"jobTitle", type text}, {"mail", type text}, {"surname", type text}, {"userPrincipalName", type text}})
in
#"Transformer les colonnes"
-------------------------------------------------------------------------------------------------------------------------------------------
but i dont get the full list of members that their userPrincipalName begins with A
could you help me pLease to extract the complete list
