cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
francesco_bip
Frequent Visitor

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?

2 REPLIES 2
francesco_bip
Frequent Visitor

Re: Power Bi Graph Connector - Paging odata.nextlink

I've already seen https://community.powerbi.com/t5/Desktop/how-to-fully-load-data-from-limited-HTTP-API/td-p/154241/pa... , but it's pretty hard to implement it in the connector.

apods1979
Frequent Visitor

Re: 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

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.