Skip to main content

Notifications

Announcements

🌸You're Invited: Community Spring Festival Awards Celebration 🌸
Replay: Power Platform Community AMA - Copilot Studio | April
Welcome to the Power Platform Communities
News and Announcements icon
Community site session details
Power Platform Community / Forums / Power Automate - Building Flows / Dynamic table range in...
Power Automate - Building Flows
Answered

Dynamic table range in Excel File

Like (1) ShareShare
ReportReport
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:

  1. When an email arrives (v3)
  2. Get Attachments
  3. Create file (SharePoint)
  4. 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
  • Verified answer
    FlowFalcon Profile Picture
    FlowFalcon 102 on 01 Nov 2024 at 14:03:52
    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 Profile Picture
    takolota1 4,859 Super User 2025 Season 1 on 25 Oct 2024 at 16:36:18
    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
  • FlowFalcon Profile Picture
    FlowFalcon 102 on 24 Oct 2024 at 11:23:25
    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 Profile Picture
    SudeepGhatakNZ 14,318 Most Valuable Professional on 23 Oct 2024 at 22:49:07
    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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

🌸You're Invited: Community Spring Festival Awards Celebration 🌸
Replay: Power Platform Community AMA - Copilot Studio | April
Welcome to the Power Platform Communities

Quick Links

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Congratulations to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 146,743 Most Valuable Professional

#2
RandyHayes Profile Picture

RandyHayes 76,287 Super User 2024 Season 1

#3
Pstork1 Profile Picture

Pstork1 66,079 Most Valuable Professional

Leaderboard

Featured topics

Start and wait for an approval action

Product updates

Microsoft Power Platform Community release plans