Overcome Power Apps Delegation Limits with Microsoft Graph API

Table of Contents

• Problem Statement
• Solution Overview
• Prerequisites
• Implementation Guide
  • Step 1: Get Site and List IDs
  • Step 2: Test with Graph Explorer
  • Step 3: Initial Search Implementation
  • Step 4: Handle Pagination
• Complete Code Examples
• Performance Considerations
• Conclusion

Problem Statement

After importing 10,000+ records into SharePoint using Power Automate bulk import techniques, we encountered Power Apps delegation limits that prevented effective searching and filtering of large datasets. There is the option of using Power Automate to get the results, however it adds more artefacts to a simple app designed for search.

Key Challenges:

• Power Apps delegation limit of 2,000 records for non-delegable functions
• Slow performance when searching large SharePoint lists
• User experience degradation with large datasets

Solution: Leverage Microsoft Graph API to bypass delegation limits and implement efficient search functionality.

Solution Overview

This solution uses Microsoft Graph API calls within Power Apps to:

• Bypass delegation limits by calling Graph API directly
• Implement server-side filtering using OData queries
• Handle large datasets with pagination support
• Improve search performance with targeted API calls
• Maintain user experience with responsive search functionality

Prerequisites

Before implementing this solution, ensure you have:

1. SharePoint list with data imported (following bulk import guide)
2. Site ID and List ID from your SharePoint environment
3. Graph Explorer access for testing API calls

Implementation Guide

Step 1: Get Site and List IDs

Retrieve Site ID using PnP PowerShell: Use the script from PnP Script Samples:

# Example Site ID format
contoso.sharepoint.com,c07cb069-eed7-4ccc-9a81-bf3856c75450,8d9eceb9-4576-4cd2-8d74-30ff3c87ec6d

Retrieve List ID using PnP PowerShell:

Get-PnPList -Identity "YourListName"
# Returns GUID format: 265c5ba9-3569-48c4-bc83-4ab76b286e97

Alternative: Use Graph Explorer to retrieve both IDs interactively.

Step 2: Test with Graph Explorer

Before implementing in Power Apps, test your Graph API calls:

Endpoint structure:

https://graph.microsoft.com/v1.0/sites/{siteID}/lists/{listID}/items

Test query with filtering:

https://graph.microsoft.com/v1.0/sites/{siteID}/lists/{listID}/items?$expand=fields&$filter=substringof('search-term', fields/Title)&$select=fields/Title,fields/Category,fields/Description

Step 3: Initial Search Implementation

Power Apps Formula (Button OnSelect or Search OnChange):

Set(
    colSearchItems,
    Office365Groups.HttpRequest(
        Concatenate(
            "https://graph.microsoft.com/v1.0/sites/{siteID}/lists/{listID}/items?",
            "$expand=fields&",
            "$filter=substringof('", txtFolderValue.Text, "', fields/Title) and fields/Category eq '", drpDatabase.Selected.Result, "'&",
            "$select=fields/Title,fields/Category,fields/Color,fields/Size,fields/Description,fields/Brand,fields/Price,fields/Currency"
        ),
        "GET",
        "",
        { ContentType: "application/json" }
    )
);

// Store pagination link for additional data
Set(varNextLink, colSearchItems.'@odata.nextLink');

// Transform and collect initial results
ClearCollect(
    colFolderMapping,
    ForAll(
        Table(colSearchItems.value),
        {
            ID: Value(Value.fields.id),
            Title: Value.fields.Title,
            Description: Value.fields.Description,
            Brand: Value.fields.Brand,
            Price: Value.fields.Price,
            Currency: Value.fields.Currency,
            Color: Value.fields.Color
        }
    )
);

Set(totalRecords, CountRows(colFolderMapping));

Step 4: Handle Pagination

By default the number of records returned by Ms Graph is 200, there is no looping (while or for) functionality using PowerFx to enable fecthing of more records up to 1000.

Timer Control Implementation (OnTimerEnd):

Update 1000 to a different number if more records are required.

If(
    !IsBlank(varNextLink) && totalRecords < 1000,
    // Fetch additional pages
    Set(
        colSearchItems,
        Office365Groups.HttpRequest(
            varNextLink,
            "GET",
            "",
            { ContentType: "application/json" }
        )
    );
    
    // Update next link
    Set(varNextLink, colSearchItems.'@odata.nextLink');

    // Append additional results
    Collect(
        colFolderMapping,
        ForAll(
            Table(colSearchItems.value),
            {
                ID: Value(Value.fields.id),
                Title: Value.fields.Title,
                Description: Value.fields.Description,
                Brand: Value.fields.Brand,
                Price: Value.fields.Price,
                Currency: Value.fields.Currency,
                Color: Value.fields.Color
            }
        )
    );
    
    Set(totalRecords, CountRows(colFolderMapping));
);

Timer properties

Complete Code Examples

Search Button OnSelect:

// Clear previous results and show loading
Clear(colFolderMapping);
Set(varLoading, true);

// Initial API call with search parameters
Set(
    colSearchItems,
    Office365Groups.HttpRequest(
        Concatenate(
            "https://graph.microsoft.com/v1.0/sites/{siteID}/lists/{listID}/items?",
            "$expand=fields&",
            "$filter=substringof('", txtSearch.Text, "', fields/Title)&",
            "$select=fields/Title,fields/Description,fields/Brand,fields/Price&",
            "$top=100"
        ),
        "GET",
        "",
        { ContentType: "application/json" }
    )
);

// Process results and set up pagination
If(
    !IsError(colSearchItems),
    Set(varNextLink, colSearchItems.'@odata.nextLink');
    ClearCollect(colFolderMapping, /* transform logic */);
    Set(varLoading, false),
    // Handle error
    Set(varError, "Search failed. Please try again.")
);

Alternative: Using Power Automate flow

There are many videos using Power Automate as a solution No Delegation Limit - SharePoint List Power App Explores using Power Automate and ParseJSON() to retrieve and display large datasets from SharePoint without hitting delegation limits.

Both Power Automate flow and Ms Graph in Power Fx can help with delegation issues.

Power Automate Flow vs. Microsoft Graph API in Power Fx

| Complexity | Low-code, user-friendly UI | Requires understanding of HTTP, JSON, and APIs | | Performance | Can be slower due to flow execution overhead | Faster, direct API calls within Power Apps | | Licensing | Standard connectors included; premium for some APIs | No option to use Premium connector | | Error Handling | Built-in error handling and retry policies | Must handle errors manually in Power Fx | | User Experience | May introduce delays due to asynchronous runs | Synchronous, immediate data retrieval | | Permissions | Possible to use child flows for elevated permissions| Runs in user’s context | | Timeout | 90 -120 days | ~minutes |

Performance Considerations

Optimization Strategies:

1. Limit Fields: Use $select to return only necessary fields
2. Page Size: Set appropriate $top parameter (default 100, max 5000)
3. Indexing: Ensure filtered fields are indexed in SharePoint lists
4. Error Handling: Implement proper error handling for API failures

Conclusion

Using Microsoft Graph API in Power Apps effectively overcomes delegation limits when working with large SharePoint lists. This approach provides:

Key Benefits: ✅ No delegation limits - Access all records regardless of size
✅ Server-side filtering - Efficient search performance
✅ Flexible querying - Complex OData filter capabilities
✅ Pagination support - Handle large result sets gracefully
✅ Better user experience - Responsive search functionality

• PowerApps
• Microsoft Graph
• SharePoint
• Delegation
• Search
• Performance
• Power Platform