Forum Discussion
Power Bi Graph Connector - Paging odata.nextlink
Good afternoon!!!
I saw many websites, chats and posts, but few are objective and each person values his method as if it were unique !!!
Each has its logic, so there is no single method !!
Objectively, for me ...
It worked this way:
Enjoy: 😘
===========================
let
tenantId = "12345678-9012-3456-7890-123456789012",
clientId = "abcdefgh-9012-3456-7890-123456789012",
clientSecret = "1234567890qwertyuiopasdf",
grantType = "client_credentials",
resource = "https://graph.windows.net/",
endpointUsers = "/users",
apiVersion = "?api-version=1.6",
baseURL="https://login.microsoftonline.com",
relativePath = "/"&tenantId&"/oauth2/token",
urlToken = baseURL & relativePath,
body = [client_id=clientId,
grant_type=grantType,
client_secret=clientSecret,
resource=resource],
//Access
Source = Json.Document(Web.Contents(urlToken, [Headers=[#"Accept" = "application/json", #"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"],Content = Text.ToBinary(Uri.BuildQueryString(body))])),
accessToken = Source[access_token],
tokenType = Source[token_type],
fullAccessToken = tokenType&" "&accessToken,
//Get Users
SourceUsers = OData.Feed(resource & tenantId & endpointUsers, [Authorization=fullAccessToken], [Implementation="2.0", Query=[#"api-version"="1.6"]]),
Step1 = Table.RemoveColumns(SourceUsers,{"createdObjects", "department", "employeeId", "facsimileTelephoneNumber", "legalAgeGroupClassification", "jobTitle", "telephoneNumber", "mobile", "givenName", "physicalDeliveryOfficeName", "consentProvidedForMinor", "sipProxyAddress", "streetAddress", "city", "state", "country", "postalCode", "ageGroup", "companyName", "preferredLanguage", "manager", "directReports", "members", "transitiveMembers", "owners", "ownedObjects", "appRoleAssignments", "licenseDetails", "oauth2PermissionGrants", "ownedDevices", "registeredDevices", "assignedLicenses", "assignedPlans", "deletionTimestamp", "immutableId", "isCompromised", "lastDirSyncTime", "passwordProfile", "provisionedPlans", "provisioningErrors", "proxyAddresses", "signInNames", "usageLocation", "userIdentities", "memberOf", "transitiveMemberOf", "thumbnailPhoto", "createdOnBehalfOf", "dirSyncEnabled", "onPremisesDistinguishedName", "onPremisesSecurityIdentifier", "showInAddressList"}),
Step2 = Table.TransformColumnTypes(Step1,{{"createdDateTime", type datetime},{"refreshTokensValidFromDateTime", type datetime}}),
Step3 = Table.TransformColumns(Step2, {"otherMails", each Text.Combine(List.Transform(_, Text.From)), type text})
in
Step3