How get a dynamic range to create a table?

(2) Share

Report

Posted on by frixel

1,089

Hello,

I created a similar post a week ago, but that was with different input that I didn't get any further with, hence this question now.

Now i have this Power Automate where I would like the number of rows to be counted after a table has been created in the file.
The table range is dynamic, which makes it very difficult for me to solve this problem.
I currently have this Flow where I don't know what to enter for "Table Range".

I hope someone is willing to help me because I can't figure out how to solve this.

Categories:

Building flows

I have the same question (0)

All responses (22)

Answers (0)

Sort by

Suggested answer

11manish 3,333 on at

Like
a
(2)

Report
Copy link

Link copied!

You don’t need to dynamically determine the table range. Instead, use a sufficiently large static range when creating the table (e.g., A1:Z10000), then use the “List

rows present in a table” action and count the rows using the length() function.

This approach avoids the complexity of dynamic ranges and works reliably with Power Automate.

Was this reply helpful? Yes No
Suggested answer

Valantis 6,735 on at

Like
a
(2)

Report
Copy link

Link copied!

Hi @frixel,

Worth flagging an issue with the large static range approach: if you define the table as A1:Z10000 but only have 50 rows of data, Excel creates the table with 9999 empty data rows included. The length() count will return 10000, not 50.

The better approach depends on how your data gets into the file:
If you know the row count before creating the table (e.g. from an array you're writing), build the range dynamically using a Compose action before the Create table step:
concat('A1:D', add(length(variables('YourDataArray')), 1))
The +1 accounts for the header row. Use that Compose output as the Table Range value.

If you just need the row count after the table already exists, use List rows present in a table and then:
length(body('List_rows_present_in_a_table')?['value'])
This returns only actual data rows, not empty ones, so it works correctly regardless of how the table range was defined.

Best regards,

Valantis

✅ If this helped solve your issue, please Accept as Solution so others can find it quickly.

❤️ If it didn’t fully solve it but was still useful, please click “Yes” on “Was this reply helpful?” or leave a Like :).

🏷️ For follow-ups @Valantis.

📝 https://valantisond365.com/

💼 LinkedIn

▶️ YouTube

Was this reply helpful? Yes No
frixel 1,089 on at

Like
a
(1)

Report
Copy link

Link copied!

As you can see in the flow, I don't know the number of rows before the table is created.
How do I get the correct number without the empty rows?

or

How can I ensure that I know the number of rows before creating a table?

Was this reply helpful? Yes No
Suggested answer

Valantis 6,735 on at

Like
a
(2)

Report
Copy link

Link copied!

Hi @frixel,

If you don't know the row count beforehand, the cleanest solution is to use an Office Script instead of the Create table action. Office Scripts can detect the used range automatically and create the table with the exact right size.

Add a "Run script" action after your data is written to the sheet, with this script:

function main(workbook: ExcelScript.Workbook) {
let sheet = workbook.getActiveWorksheet();
let range = sheet.getUsedRange();
sheet.addTable(range.getAddress(), true);
return range.getRowCount() - 1;
}

This finds whatever data is already on the sheet, creates the table around it exactly, and returns the row count excluding the header. No empty rows, no guessing the range.

The return value gives you the accurate row count you can use in the rest of your flow.

Note: Office Scripts requires a Microsoft 365 Business Standard license or higher.

Best regards,

Valantis

✅ If this helped solve your issue, please Accept as Solution so others can find it quickly.

❤️ If it didn’t fully solve it but was still useful, please click “Yes” on “Was this reply helpful?” or leave a Like :).

🏷️ For follow-ups @Valantis.

📝 https://valantisond365.com/

💼 LinkedIn

▶️ YouTube

Was this reply helpful? Yes No
frixel 1,089 on at

Like
a
(1)

Report
Copy link

Link copied!

@Valantis,

Script must be saved i a file? 😒

Was this reply helpful? Yes No
Suggested answer

Kalathiya 2,456 Super User 2026 Season 1 on at

Like
a
(2)

Report
Copy link

Link copied!
Hello @frixel,

You can create a dynamic table using the OFFSET function as well, but in that case, you need to ensure that all column headers are already defined in the Excel sheet.

As mentioned in the other thread, the Create table action requires at least the header row otherwise it will fail.

=OFFSET(Sheet1!A1,0,0,SUBTOTAL(103,Sheet1!$A:$A),3)

Thread: https://community.powerplatform.com/forums/thread/details/?threadid=17adb0a2-b23f-f111-88b5-0022482aa957&sort=newest

If this response resolves your issue, please mark it as the Verified Answer so it can help other community members as well.

---------------------------------------------------------------------------------

📩 Need more help? Just mention @Kalathiya and I’ll be happy to assist.

✔️ If this answer helped you, please tick “Does this answer your question?” so it can be marked as the Verified Answer.

💛 A Like always motivates me to keep contributing!

Was this reply helpful? Yes No
Suggested answer

Valantis 6,735 on at

Like
a
(2)

Report
Copy link

Link copied!

Hi @frixel,

Yes, the script needs to be saved first. You'd write it in the Office Scripts editor inside Excel Online (Automate tab), save it there, and then it shows up in the Script dropdown in the Run script action in Power Automate.

That said, Kalathiya's OFFSET approach is actually simpler and doesn't require Office Scripts at all. If your column headers are already defined in the sheet, use that directly in the Table Range field:

=OFFSET(Sheet1!A1,0,0,SUBTOTAL(103,Sheet1!$A:$A),3)
Just replace the 3 at the end with your actual number of columns. That detects the used rows automatically and is the cleaner solution for your case.

Best regards,

Valantis

✅ If this helped solve your issue, please Accept as Solution so others can find it quickly.

❤️ If it didn’t fully solve it but was still useful, please click “Yes” on “Was this reply helpful?” or leave a Like :).

🏷️ For follow-ups @Valantis.

📝 https://valantisond365.com/

💼 LinkedIn

▶️ YouTube

Was this reply helpful? Yes No
frixel 1,089 on at

Like
a
(1)

Report
Copy link

Link copied!

@Kalathiya

Yes i know that you suggested that but alway`s i will try with the OFFSET formule the flow is failed

Sheet name = Worksheet an have already the same headers

Was this reply helpful? Yes No
Suggested answer

Valantis 6,735 on at

Like
a
(2)

Report
Copy link

Link copied!

Hi @frixel,

The OFFSET formula looks correct. BadRequest from Create table usually means one of two things:

1. A table already exists on that sheet. If the flow ran before and created a table, running it again tries to create another table in the same range which Excel rejects. Add a "Delete table" action before the Create table step, or check if a table is already there.

2. The column names in the "Columns names" field don't exactly match the headers in row 1 of the sheet. Every column name must be an exact character-for-character match including spaces and capitalisation. If even one doesn't match it throws BadRequest.

Can you check those two things? If the sheet is fresh each time (created by the Create file action before this), then it's almost certainly the column names mismatch.

Best regards,

Valantis

✅ If this helped solve your issue, please Accept as Solution so others can find it quickly.

❤️ If it didn’t fully solve it but was still useful, please click “Yes” on “Was this reply helpful?” or leave a Like :).

🏷️ For follow-ups @Valantis.

📝 https://valantisond365.com/

💼 LinkedIn

▶️ YouTube

Was this reply helpful? Yes No
Kalathiya 2,456 Super User 2026 Season 1 on at

Like
a
(2)

Report
Copy link

Link copied!

Hello @frixel,

There doesn’t seem to be an issue with the sheet name.

Could you please share a screenshot of your Excel sheet?

From your scenario, it looks like the issue might be that column headers are not defined. When using the OFFSET function, Power Automate does not create columns automatically, it expects a valid range with headers already present.

If no headers exist, the Create table action will fail because due to column not exist in sheet.

If this response resolves your issue, please mark it as the Verified Answer so it can help other community members as well.

---------------------------------------------------------------------------------

📩 Need more help? Just mention @Kalathiya and I’ll be happy to assist.

✔️ If this answer helped you, please tick “Does this answer your question?” so it can be marked as the Verified Answer.

💛 A Like always motivates me to keep contributing

Was this reply helpful? Yes No