Read Rows in Excel Using Power Automate with ‘Create Table’ Action

When working with Excel files that need to be imported into a SharePoint list, you may encounter scenarios where the data is not already in table format. This can be challenging, especially if the Excel files are automatically generated by an external system and cannot be manually updated.

This blog post demonstrates how to dynamically convert raw data into a table using the Create Table action in Power Automate and subsequently read rows using the List rows present in a table action.

Challenges with Non-Table Data

If the data in your Excel file is not in table format, you have two options:

1. Office Scripts: While Office Scripts can automate the process, they are saved in the user’s OneDrive and require TypeScript, adding complexity to deployment and flow logic.
2. Create Table Action: This action converts raw data into a table format, making it compatible with the List rows present in a table action. This is described within Power Automate - How to create Excel table dynamically (Excel Formula)?.

This post focuses on option 2.

Step 1: Create Table

To handle an indefinite number of rows in the Excel file, use the OFFSET function to dynamically define the table range.

OFFSET Formula

=OFFSET(<SheetName>!A1,0,0,SUBTOTAL(103,<SheetName>!$A:$A),<Numberofcolumns>)

Explanation:

• <SheetName>!A1: The starting reference point for the range.
• 0,0: Indicates no offset from the starting reference point (i.e., start at A1).
• SUBTOTAL(103,<SheetName>!$A:$A): Counts the number of active rows (rows with data) in column A.
• <Numberofcolumns>: Specifies the number of columns in the data range.

Result

Step 2: List Rows Present in a Table

Once the table is created, use the List rows present in a table action to read the data.

Notes:

• If the file triggering the flow is the same file being processed, reference it using triggerBody()?['{Identifier}'].
• Use the same table name specified in the Create Table action to ensure consistency.

The drive Id is populated

Step 3: Add Subsequent Actions

After retrieving the rows, you can use an Apply to each action to loop through the rows and perform operations such as creating items in a SharePoint list.

Benefits of This Approach

• Dynamic Table Creation: Automatically converts raw data into a table format, eliminating the need for manual updates.
• Scalable: Handles an indefinite number of rows dynamically using the OFFSET function.
• Integration: Seamlessly integrates with SharePoint and other Power Automate actions.

Reference

• PowerAutomate
• Import Data
• Excel for Business
• OFFSET Excel Function