Forum Discussion
mongoose20
Microsoft
MicrosoftChoosing a range from a table
Here is an interesting puzzle:
Choose the first 10 rows of a table but not based on any criteria in the table. Not the top or bottom 10 based on a second column. We just want the the first ten rows. Here is where it gets harder. If there are less that 10 row if should returns just the number of rows the table has. I can think of two ways to approach this:
Solution 1:
Format the raw data as a table. Reference the table column i.e. TableName[Column Name]. Can the first ten rows be chose using this formula? Can the individual rows be called like members of an array in e.g. Python?
Solution 2:
Choose ten cell as a spill area where the result should be. E.g., =A1:A10. A dynamic number of rows will show but with 0 for empty ones. The problem with this approach is that if there are less that ten rows, 0 will be inserted.
Thoughts?
mongoose20SergeiBaklan - thank you so much !
mongoose20 , you are welcome
mongoose20 How about this?
=TAKE(Table1[#All],11)It will return the headers plus the first 10 rows, or fewer if there aren't 10 rows in the table.
Or if you want to make it unnecessarily complicated:
=CHOOSEROWS(Table1[#All],SEQUENCE(MIN(ROWS(Table1),10)+1))
