Power Automate

Create an Excel table failed error.

(0) Share

Report

Posted on by Sormick

566

Hello,

I need to create a dynamic table from an excel workbook but my flow has failed with an error on "CREATE A TABLE":

"The “Create_a_table” action failed: The argument is missing or invalid, or has an incorrect format. clientRequestId:..."

On the "TABLE RANGE" i put this formula to get all the contents from the sheet named "Report".

27 columns needed to create my Table.

=OFFSET(Report!A1,0,0,SUBTOTAL(103,Report!$A:$A),27)

Do you know what is missing here on my flow?

Categories:

Building flows

I have the same question (0)

All responses (8)

Answers (0)

Sort by

Suggested answer

NsL Coder 475 Moderator on at

Like (0)

Report
Copy link

Link copied!

the "Table range" field is looking for A1 notation of the rows/columns that will be created as table, so it is not looking for a "formula".

FOr example:
A1:F10

Was this reply helpful? Yes No
Sormick 566 on at

Like (0)

Report
Copy link

Link copied!
@NsL Coder Thank you so much for your reply.

I confirm , this function:

=OFFSET(Report!A1,0,0,SUBTOTAL(103,Report!$A:$A),27)

Does not work on Power automate.

Please note that i use a French version of the Power automate.

Do i need to convert ths unction above on a french version?

Is this function outdated?

Was this reply helpful? Yes No
Suggested answer

NsL Coder 475 Moderator on at

Like (0)

Report
Copy link

Link copied!
Apology, I re-read your original post again and looked at your formula. The A1 notation that the Create table expects actually does allow forumla (like OFFSET) that returns a reference to a "range".

So "OFFSET" does work, however, the error you are getting can be a few things, so you will need to check:

is Report a worksheet in the excel you indicated

I don't use a french version of excel so I don't know if french version of excel have the formula spelled diferently. You may want to make sure of that directly in excel and see if you can type in th formula (OFFSET and SUBTOTAL).

Was this reply helpful? Yes No
Sormick 566 on at

Like (0)

Report
Copy link

Link copied!
Hello,

I'm sorry , i did not noticed that my formula was looking only for A1 cell range only.

Please, what i need to change on my formula to create a "Dynamic" table on my workbook.

If the function "=OFFSET" is no more valid what is the right function to be used for this use case?
I tested this french formula which works on my local workbook but does not work on the power automate
=DECALER(Report!A1;0;0;SOUS.TOTAL(103;Report!$A:$A);2)

Was this reply helpful? Yes No
Suggested answer

NsL Coder 475 Moderator on at

Like (0)

Report
Copy link

Link copied!

I don't think the formula is incorrect.

try instead of ; (semicolon) with , (comma)

=DECALER(Report!A1, 0, 0, SOUS.TOTAL(103, Report!$A:$A), 2)

Was this reply helpful? Yes No
Sormick 566 on at

Like (0)

Report
Copy link

Link copied!

@NsL Coder

unfortunately this formula with comma but the flow still failed

I will try to get mor information on the net about the right formula to be used.

I'll put the results here if i find the right formula.

Was this reply helpful? Yes No
Sormick 566 on at

Like (0)

Report
Copy link

Link copied!
Dear @NsL Coder

What do you mean with "so it is not looking for a formula"

Can i still create a dynamic table with a formula?

What i need to do for this to work properly without issues?

I changed my formula to this:

=OFFSET(Items!A1,0,0,SUBTOTAL(103,Items!$A:$A),1)

Because my sheet name is: "Items"

Was this reply helpful? Yes No
NsL Coder 475 Moderator on at

Like (0)

Report
Copy link

Link copied!

I am in the United States, using enligh version of Excel. I know the dynamic formula of OFFSET does work as I tried it in my PA flow.

I cannot help much more as I don't know how the "language" difference affect your formula and/or how flow intakes the info.

Good luck.

Was this reply helpful? Yes No