Dynamic table range in Excel File

(1) Share

Report

Posted on by FlowFalcon

143

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:

Building flows

I have the same question (2)

All responses (4)

Answers (1)

Suggested answer

SudeepGhatakNZ 14,394 Most Valuable Professional on at

Like (1)

Report

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

Was this reply helpful? Yes No
FlowFalcon 143 on at

Like (1)

Report

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,

Was this reply helpful? Yes No
takolota1 4,974 Moderator on at

Like (1)

Report

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

Was this reply helpful? Yes No
Verified answer

FlowFalcon 143 on at

Like (2)

Report
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.

Was this reply helpful? Yes No