How to Extract 50,000 Excel Rows in Seconds Using Power Automate and Graph API

When your Power Automate flow needs to read more than a few thousand rows from an Excel file stored in SharePoint or OneDrive, the built-in List rows action quickly becomes a performance bottleneck. I've seen flows time out or crawl for minutes just to fetch 20,000 rows. Switching to the Microsoft Graph API via the HTTP with Azure AD connector shrinks that time to seconds — in my tests, 50,000 rows came back in under 8 seconds.

This article walks through a complete, production-ready pattern: you'll set up a real-world scenario, retrieve the file identifiers, make a raw Graph API call, and then reshape the nested JSON response into clean, usable records.

Example Scenario: Reconcile Project Budgets

Imagine a finance automation that reads budget allocations from a large Excel table named tblProjectBudget and pushes them into a Dataverse table. The file lives in a SharePoint document library called "Finance Reports".

The table has five columns:

• ProjectCode – short identifier (e.g., PRJ-4021)
• Department – department name
• BudgetAllocated – currency amount
• SpentToDate – currency amount
• Status – plain text ("Active", "On Hold", etc.)

For the demo, we'll populate the table with 50,000 rows of realistic but generated data. The technique works for up to about 100,000 rows per call, well beyond what the Excel connector supports.

Step 1: Retrieve the Drive and Item IDs

To call the Graph API endpoint for an Excel file, you need its Drive ID (the SharePoint site's document library identifier) and its Item ID (the file's identifier inside that library). The quickest way to get both is to use the Excel connector once — but we'll ask it to return zero data.

Add the List rows present in a table action (Excel for Business Online). Point it to your file and to tblProjectBudget. Set Top count to 1. The action will run and you can discard the row data; we only care about the inputs stored in the run history.

After a test run, inspect the action's inputs. You'll find two GUIDs:

• drive: something like b!K2d9Hv...
• file: something like 01AZ...

Capture these by adding two Compose actions. Use the following Power Automate expressions (adjust the action name to match yours):

actions('List_rows_present_in_a_table:_tblProjectBudget')?['inputs']?['parameters']?['drive']

actions('List_rows_present_in_a_table:_tblProjectBudget')?['inputs']?['parameters']?['file']

Now you have the two values available as outputs('Compose:_Drive_ID') and outputs('Compose:_Item_ID').

Step 2: Configure the HTTP with Azure AD Connection

Add the HTTP with Azure AD – Invoke an HTTP request action. When prompted for the connection, fill in both fields with the same URL:

https://graph.microsoft.com

This tells the connector that every request will go to the Microsoft Graph API root. The action will automatically handle authentication with the signed-in user's context.

Step 3: Read All Table Rows from the Excel File

Now craft the GET request. Set Method to GET. For the Uri, build the following string, referencing your two Compose outputs:

v1.0/drives/@{outputs('Compose:_Drive_ID')}/items/@{outputs('Compose:_Item_ID')}/workbook/worksheets/tblProjectBudget/tables/tblProjectBudget/rows

Important: Replace the worksheet name if your table is on a sheet other than the default. The URL pattern uses worksheets/tblProjectBudget/tables/tblProjectBudget — the first tblProjectBudget is the sheet name, and the second is the actual table name. If your table is on Sheet1, adjust accordingly:

v1.0/drives/@.../items/@.../workbook/worksheets/Sheet1/tables/tblProjectBudget/rows

Run the flow once to confirm you get a 200 response with a value array containing all rows. Each row is represented as a JSON object with a values property that holds an array of arrays.

Step 4: Parse and Flatten the JSON Response

The raw response looks like this:

{
  "value": [
    {
      "index": 0,
      "values": [ [ "PRJ-4021", "Finance", 125000, 83000, "Active" ] ]
    },
    ...
  ]
}

To work with the data, you need to:

1. Extract the value array from the HTTP response.
2. Flatten each values subarray into a simple array.
3. Map array positions to column names.

4.1 Parse the outer array

Add a Parse JSON action. Set Content to the expression:

body('Invoke_an_HTTP_request')?['value']

Run a test, copy the sample output, and use Generate from sample to build the schema. The schema should define an array of objects with at least an index and values property (where values is an array of arrays).

4.2 Flatten the nested arrays

Add a Select action. Switch the Map property to Text mode and enter:

first(item()?['values'])

This transforms the array of objects into a simple array of arrays — each inner array contains the five cell values in order.

4.3 Create structured records

Add a second Select action to map the positional values to named properties. This time, because we're using the text-mode mapping, you'll define each column manually.

Use the following expressions for each column (adjust indices based on your table's column order):

ProjectCode:  item()[0]
Department:   item()[1]
BudgetAllocated:  item()[2]
SpentToDate: item()[3]
Status:       item()[4]

The output of this action is a clean array of objects with the column names as properties.

4.4 (Optional) Make columns appear in the dynamic content picker

If you want the columns to show up in the Power Automate designer for subsequent actions, add one more Parse JSON action. Use the output from the second Select as its Content, run the flow again, and generate a schema. The dynamic content will then include ProjectCode, Department, etc.

Security and Performance Considerations

• Required Graph API permissions: The HTTP with Azure AD connector uses the permissions granted to the user (or service principal) that created the connection. At a minimum, you need Files.Read or Sites.Read.All (if the file is on SharePoint). For a service principal, assign the appropriate application permission.
• Throttling: Graph API has limits (e.g., 10,000 requests per 10 minutes per app). Reading rows from a single table is a single request, so throttling is rarely an issue for this pattern.
• Why is it faster? The Excel connector translates each action into multiple Graph calls and applies additional processing. By calling the raw API, you eliminate that overhead and retrieve the entire table payload in one response.
• Row limit: The documented maximum for the rows endpoint is provisionally 100,000 rows. Beyond that, you'd need to paginate using $skip and $top. For most real-world tables, 50,000 rows per call is safe.

Common Mistakes and Troubleshooting

• Wrong table or worksheet name: Double-check the endpoint URL. A typo in the table name returns a 404 or "Item not found". Inspect the Excel file to confirm the exact table and sheet names.
• Parse JSON schema failure: If the "Generate from sample" step doesn't produce a valid schema, manually add a schema for an array of objects with an index (integer) and values (array of arrays of strings/numbers). The nested values array will be flattened later, so you don't need to model it deeply.
• Empty array after flattening: If the first Select returns only an empty array, verify that the Parse JSON action correctly extracts body('Invoke_an_HTTP_request')?['value'] and that the HTTP response actually contains data. Also, the first(item()?['values']) expression requires values to be a non-empty array.
• Index out of range: If you map to item()[5] but your table has only four columns, the expression will fail at runtime. Keep column count in sync.

Conclusion

The Excel connector is convenient for small lookups, but for any flow that processes thousands of rows, the Graph API approach is the clear winner. It's reliable, respects your existing authentication, and can turn a five-minute time-out into a seven-second breeze.

Adopt this pattern for your data-heavy automations — your morning runs will finish before you pour your coffee.

References

• Matthew Devaney's original article that inspired this technique: Fastest Way To Read Large Excel Table In Power Automate
• Microsoft Graph API Excel workbook reference (table rows): List tableRowCollection
• HTTP with Azure AD connector documentation: Microsoft Learn