Handling Graph API Pagination in Power Platform Dataflows

Handling Graph API Pagination in Power Platform Dataflows

Introduction

When managing extensive user datasets from Microsoft Graph API, a common challenge is handling the pagination of data. This blog post explores a solution for effectively looping through multiple pages of Graph API data within Power Platform dataflows and discusses alternative methods that might be more efficient in certain scenarios.

Background

The need for this project arose from our requirement to comprehensively collect and set manager information for our users in Dataverse. This was integral to improving our workflow automation and communication processes. However, the pagination feature in Graph API responses presented a significant challenge in retrieving this data efficiently, prompting us to seek a solution that could integrate seamlessly with Dataverse and optimize our data management practices.

Exploring Graph API with Graph Explorer

To become proficient in crafting queries for the Microsoft Graph API, a powerful resource at your disposal is the Graph Explorer tool. This interactive tool allows you to formulate and test Graph API queries in a user-friendly environment. It provides a practical hands-on approach to learning how the API responds to different queries and helps you understand the structure of the data it returns.

By experimenting with Graph Explorer, you gain valuable insights into how Graph API operates, enabling you to build more effective queries for your Power Platform dataflows. Whether you’re retrieving user data, managing tasks, or accessing analytics, the Graph Explorer can be your sandbox for mastering Graph API interactions.

Solution: Implementing Looping Logic and Considering Alternatives

1. Understanding Graph API Pagination

  • Microsoft Graph API uses pagination to manage large datasets, providing links to subsequent pages in each response.
  • Effectively handling this is crucial for comprehensive data collection.

2. Setting Up the Graph API Connection

  • The first step involves integrating the Graph API into your Power Platform dataflow.
  • Create a new Dataflow in the Maker portal.
    image
  • Select the Web API connector
    image
  • Create a connection to the Graph API url (eg: https://graph.microsoft.com/v1.0). Make sure to get the authentication type to Organizational account.
    image
  • A new query will be created showing all of the endpoints for the Graph API.
    image

Now that you have a connection to graph you can utilze the default query that was provided for you or create your own queries using the pattern provided by this default query and using the Json.Document(Web.Contents(“https://graph.microsoft.com/v1.0/me”)) functionality to pass in your Graph query.

3. Looping Through Paginated Data

  • Develop a script or logic within your dataflow to process each page and retrieve the next page’s link.
  • The code below loops through all users within Entra ID and get all manager information. You can utilize the Advanced editor button in the query window to copy/paste this code.
let
    // This query will return all users who are domain members and have a usage location with the US.
    // Expand gets us the mail attribute for the users first level manager.
    // Top is set to 999, this is the max size of records returned per page for this call and will result in fewer calls having to be made.
    url = "https://graph.microsoft.com/v1.0/users?$filter=userType eq 'Member' and usageLocation eq 'US' &$select=userPrincipalName&$expand=manager($levels=1;$select=mail)&$top=999",

    // This function will return the data for each page.
    FnGetOnePage = (url) as record =>
        let
            Source = Json.Document(Web.Contents(url)),
            data = try Source[value] otherwise null,
            next = try Record.Field(Source, "@odata.nextLink") otherwise null,
            res = [Data=data, Next=next]
        in
            res,

    // This calls the function to return data for each page that is returned and creates the combined list.
    GeneratedList = List.Generate(
        ()=>[i=0, res = FnGetOnePage(url)],
        each [res][Data] <> null,
        each [i=[i]+1, res = FnGetOnePage([res][Next])],
        each [res][Data]
    ),

    // Create a combined list
    CombinedList = List.Combine(GeneratedList),
    // Convert the list into a table format
    #"Convert To Table" = Table.FromList(CombinedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    
    // Assuming the data structure is a record, expand the columns you need. Adjust the column names based on your data structure.
    #"Expand Each Record" = Table.ExpandRecordColumn(#"Convert To Table", "Column1", {"userPrincipalName", "manager"}, {"userPrincipalName", "manager"}),
    // Expand the manager record to get their email address
    #"Expand Manger" = Table.ExpandRecordColumn(#"Expand Each Record", "manager", {"mail"}, {"mail.1"}),
    #"Renamed columns" = Table.RenameColumns(#"Expand Manger", {{"mail.1", "ADUser.ManagerEmail"}, {"userPrincipalName", "ADUser.Upn"}})

in
    #"Renamed columns"

4. Considering Alternatives

  • Looping through pages is straightforward, but not always the most efficient.

    Alternative Approaches:

    • Batch Processing: Send multiple requests in a single call using the Microsoft Graph batch processing feature.
    • Delta Query: Use delta queries to fetch only changes since the last query, reducing data volume.
    • WebJobs or Azure Functions: For more control, consider Azure services like WebJobs or Azure Functions.

5. Conclusion and Optimization Tips

  • While looping is effective, alternatives could offer more efficiency for specific use cases or large-scale operations.
  • Evaluate your project needs to choose the most suitable method.

Final Note

Assess your project’s specific requirements to determine whether looping through pagination or an alternative approach is more appropriate. This decision can significantly impact the efficiency and scalability of your data management in Power Platform and Graph API.

Comments

Post a Comment

Popular posts from this blog

Add User As Local Administrator On Domain Controller

I recently was settting up a new Microsoft SharePoint 2010 machine and had promoted the machine to a domain controller before creating my SharePoint admin accounts.  I needed to add several of my accounts to the local Administrators group.  Unfortunately after you promote a server to a domain controller you can no longer access the GUI for Local Users and Groups.  Instead I had to use the command line to add the users. Open a command promt using the "Run as administrator" function and then run the following command. net localgroup Administrators /add {domain}\{user} Note: do not include the {} brackets.
Read more

Calling Dataverse Web API in PowerShell using Client Credentials

Image
Connecting to Dataverse using PowerShell can be very helpful for data migrations and use within Azure DevOps. Connecting to an instance in a non-interactive way can be tricky though. This article will provide you the links you need for creation and App registration and adding the app user to your environment. You can then utilize the script provided to call Web API requests including ones you define using the new Custom API functionality now available. The script was written so that it is not dependent on any outside libraries such as the Microsoft.Xrm.Tooling connector. This is helpful in situation where involving an outside library will slow down your deployment time by having to be approved in a change control board. If utilizing an outside code library is not a concern you can create a connection to Dataverse utilizing the Microsoft.Xrm.Tooling connector Get-CrmConnection cmdlet. Create App Registration The first thing to do is create an App Registration within Azure AD for
Read more

Windows Server 2008R2 VMs Shut Down After 1 to 2 Hours

Image
When created a lab environment to test ADFS 2.0 I utilized the Windows 2008R2 VM baselines distributed by Microsoft.  After a few days I was told that I had to activate.  The VMs included a 180 day license for use but I didn't feel like adding another network adapter into Hyper-V to connect them to the internet.  I started having issues though where the servers would shut down every hour or so.  I though that maybe there was a memory issue and Hyper-V was shutting them down in order to free up RAM.  Turns out that if Server 2008R2 is not activated it automatically shuts down after a period of time.  After connecting the server to the internet and activating them the problem went away. Finally the madness of the unknown shut downs has ended :)
Read more