Power Automate - Building Flows

Answered

Dynamic table range in Excel File

Like (1) Share

Report

Posted on 23 Oct 2024 21:54:43 by FlowFalcon

102

Hello everyone,

I have an XLSX Excel file with 30 columns, and the number of rows varies (usually fewer than 4,000 records). I created a simple flow with the following actions:

When an email arrives (v3)
Get Attachments
Create file (SharePoint)
Create table (Excel Online Business)

If I set a fixed range for the "Table range" property in the Create table action, the table is created without any issues. For example:
Sheet1!A1:AD3723

However, this approach isn't useful because the file I receive by email varies in the number of rows.

I tried using the following formula I found online:
=OFFSET(Sheet1!A1,0,0,SUBTOTAL(103,Sheet1!$A:$A),30)

Unfortunately, it doesn’t work and returns the following error in the flow:

"body": {
    "status": 400,
    "message": "The argument is invalid or missing or has an incorrect format.\r\nclientRequestId: 2efa7406-3979-78o0-ac6f-1beb8b8ce828\r\nserviceRequestId: c4b13ba2-f8b8-4496-88af-8d67005ea64e;3faf92f5-f3dd-4566-8a07-1b76b2386ce7",
    "error": {
        "message": "The argument is invalid or missing or has an incorrect format."
    },
    "source": "excelonline-cus.azconn-cus-001.p.azurewebsites.net"
}

Is there a formula or expression I can use that will work?

I would really appreciate your help.

Thank you!

Categories:

Business Process Flows

All responses (4)

Answers (1)

Verified answer

FlowFalcon 102 on 01 Nov 2024 at 14:03:52

Like (2)

Report
Dynamic table range in Excel File

Hi @AlexEncodian @takolota1 @SudeepGhatakNZ

I cannot use third-party connectors, such as Encodian, due to data privacy concerns. I already had a script that converts the Excel file to a table, but I wanted to try a more direct solution (perhaps more professional?). I opted to use Microsoft Graph.
Here I share how I implemented it. If you have any questions or if something is unclear, let me know.

INVOKE AN HTTP REQUEST: It returns the number of records that has the Excel sheet.

CREATE TABLE:

ID: ID from create file (@outputs('create_file')?['body/Id'])

TABLE RANGE: This is a custom dynamic value (@{body('Invoke_an_HTTP_request')?['address']})

TABLE NAME: this is optional. I use a variable for internal purposes.
takolota1 4,863 Super User 2025 Season 1 on 25 Oct 2024 at 16:36:18

Like (1)

Report

Dynamic table range in Excel File

If just need to get the table data at any point, then you could use an Office Script like this template

https://community.powerplatform.com/galleries/gallery-posts/?postid=9f7a7bcf-c88f-ef11-ac20-7c1e525bd67d
FlowFalcon 102 on 24 Oct 2024 at 11:23:25

Like (1)

Report

Dynamic table range in Excel File

Hi @SudeepGhatakNZ, @SudeepGhatakNZ thank you for your response.

I'm having trouble understanding your instructions. I cannot use the expression length(body('List_rows_present_in_a_table')?['value']) since I don't have the table set up yet - creating the table is precisely what I need to do.

Would you be able to provide some screenshots or more detailed step-by-step instructions? That would help me better understand the process.

I look forward to your response.

Thank you again for your help.

Best regards,
Suggested answer

SudeepGhatakNZ 14,318 Most Valuable Professional on 23 Oct 2024 at 22:49:07

Like (1)

Report

Dynamic table range in Excel File

Instead of using Excel formulas directly in Power Automate, you can use a combination of Power Automate actions to find the last row in your data.

Add an action to count the number of rows returned by the previous step. Use an expression like:

length(body('List_rows_present_in_a_table')?['value'])

Set the Table range property using an expression that combines the first column and the last row count:

concat('Sheet1!A1:AD', outputs('Get_row_count'))
Let me know if that works

Community site session details

Dynamic table range in Excel File

Helpful resources

News and Announcements

Quick Links

Announcing the Engage with the Community forum!

🌸 Community Spring Festival 2025 Challenge Winners! 🌸

Warren Belz – Community Spotlight

Leaderboard > Power Automate - Building Flows

Featured topics

Product updates

Community site session details

Dynamic table range in Excel File

Helpful resources

News and Announcements

Quick Links

Announcing the Engage with the Community forum!

🌸 Community Spring Festival 2025 Challenge Winners! 🌸

Warren Belz – Community Spotlight

Subscribe to this forum!

Select categories

Leaderboard > Power Automate - Building Flows

Featured topics

Product updates