You’re offline. This is a read only version of the page.
Hi,
Every day, we save an Excel file on our Sharepoint environment and create an Excel Table in the file.
The content of the Excel file is different every day. Different number of rows as well.
When I use the "Create Table" node from the Excel Online package, I need to specify the Table Range.
Every file contains columns A to column DN. But the rows are different.
It tried the table Range "A:DN" but when I do that, it creates a lot of empty rows at the end of the data table. So I think I have to specify the number of rows as well.
For example today I have 412 rows. So the table range is "A$1:DN$412".
My question is: Do I really need to specify the number of rows in the table range? and if so: How can I extract the number of rows from an Excel file?
Thanks!
Marco
Turns out there is a way!!
In the "Table Range" we decided to use an excel formula: =OFFSET('SheetName'!A1,0,0,SUBTOTAL(103,'SheetName'!$A:$A),16)
We did specify the number of columns (16), however, the number of rows is now dynamic. Tested and functioning!!
Dear all,
2 factors explain the hurdle :
See my post above, about Office Scripts. You create a new script with the code I provided and then call on the script in Power Automate to add the table. The file must be in SharePoint or Onedrive for this to work.
Same issue same date.
666lestat => I didn't understand your change "coma by semicolon" ...you mean, in the formula ?
like
=OFFSET(Orders!A1;0;0;SUBTOTAL(103;Orders!$A:$A);45)
instead of
=OFFSET(Orders!A1,0,0,SUBTOTAL(103,Orders!$A:$A),45)
I did the change BUT not working
Mine is working fine. Could it be possible that the environment you use is not production?
You could be right about the MS Update, I personally have not noticed it breaking. The first thing I would do is take a peek at the incoming data on the spreadsheet. Things can go haywire if you are getting a blank row in between populated rows, and sometimes even a blank cell when a value is expected to be there.
#1
WarrenBelz
146,788
Most Valuable Professional
#2
RandyHayes
76,287
Super User 2024 Season 1
#3
Pstork1
66,093
Most Valuable Professional
Share
Report
All responses (
Answers (