Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Power Bi Graph Connector - Paging odata.nextlink

Hi, I used this guide "https://docs.microsoft.com/en-us/power-query/samples/mygraph/readme" to create a power bi connector to microsoft graph api.

Everithing works, but when I call an api with more than 999 rows, like "https://graph.microsoft.com/beta/users", I receive the odata.nextlink to retrieve the others pages.

The connector works with 4 functions:

startlogin, finishlogin, refreshtoken and logout.

I didn't manage to call odata.nextlink and append all the pages in one table.

Any help?

Need Help

2 Replies

  • apods1979's avatar
    apods1979
    Helper I
    6 years ago

    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