DataTable row count

[akosiwong]

Hi,

How to count the numbers of rows in a DataTable?

Thanks,

Gilbert

[martinav]

So, to summarize... a table object is not really a table for countrows.  Is that correct?  Also, its no good if you have over 500 items resulting in your filter, because it is subject to delegation.

Is there another way to do this to get a full count?

[ProtivitiDan]

It's a shame we can't use DataTable1.Items as the input to the CountRows function. I have a fairly complex Filter formula in my table Items property. I have to copy that formula to the label where I want to show the count which is not good for maintainability.

[timl]

Hi @ProtivitiDan 

I understand what you're saying - it would be much better if it were possible to access DataTable1.Items. Perhaps this is something that you could post in the ideas forum?

https://powerusers.microsoft.com/t5/PowerApps-Ideas/idb-p/PowerAppsIdeas

Here's something else you could try (although I've not tested it thoroughly). The gallery control exposes a property called AllItems. Therefore, you could add a gallery control to your screen and set the Items property of the gallery to your complex Filter formula. You can then set the visible property of your gallery to false, and then set the Items property of your data table to the AllItems property of the gallery. In your label, you can carry out a CountRows on the AllItems property of the gallery.

That way, there will only be a single instance of your complex filter formula, and it would reduce the risk of you updating the formula in the data table, and forgetting to update the CountRows formula in your label. 

[ProtivitiDan]

Thanks @timl I posted an idea.

I have considered just switching to use a gallery control instead of the data table. There are advantages to each, and having the AllItems property is certainly an advantage of the gallery over the data table. Your suggestion is an interesting alternative in using both that I might explore more.

[FUS]

I have the same challange.

I have a source with about 4000 rows and I filter this in a table to get a portion of the data... say 75 rows.
Countrows with the same filter as the table works... but the problem is that the delegation doesn't work.

Since the app already have a table with the 75 rows... I would really want to take advantage of this and have a label that says it's 75 rows... without reaching for the data source to process 4000 rows again.

[martinav]

@FUS ,

I have been able to get my items counted by making data into a collection first.  Once you get your data into a collection, you can do about anything quite quickly.  Manipulation within collections are all done internal to  your app.  The challenge is getting the data into your app quickly.  I have several methods to do this now.  All have their pros/cons.  Let me know if any of these methods may be beneficial.

1. Use MS Flow to collect all of  your data, then push it into your app.  You can get thousands of rows like this, but it takes a bit to do it when you load your app.

2. Use MS Flow scheduled at an interval.  Collect thousands of rows of data, then push into a sharepoint file as JSON array.  This is VERY fast to retrieve into PA, but data is not "live".  Data is as old as your interval period.  

These methods are published here, but I have made improvements.  If you have a preferred method, let me know, and I can perhaps update them.

[FUS]

@martinav 

Hmm, for the purpose I got I think I might just remove the counter, since it's just a nice to have function.

But I'm curious on your solutions with using flow and would love to read more about them.
Do you by any chance have a link to those posts?

[martinav]

@FUS ,

This writeup is for method 1.

https://powerusers.microsoft.com/t5/Building-Power-Apps/Another-method-for-making-a-collection-from-...

This method takes 20sec to execute, and retrieves 2,671 rows currently. 

Method 2. - pushing data into Sharepoint file as a JSON array.

This takes just over 20 seconds.  Its basically the same flow thats in the writeup, but it on a 15min schedule, and makes a file:

1. Every 15min, a very similar flow runs to collect the same 2,671 rows of data.

2. The data is formatted into JSON Array

3.  The array is pushed to a Sharepoint file.

When Powerapps needs the data, it executes a flow to pull the data from SP, then pushes it into Powerapps via a Response action.

This takes around 2 seconds (Same data as method 1):

1.  Initiate a flow action in a Collect() statement.

2.  Flow get the file using Get File Content Action.

3.  File Content dynamic content is put into the Response action.  

4.  All 2,671 rows of data are now in the collection.  

[Rabbanisyed]

Most of the time wrong solutions were given, your simple question which i have the same question and  wanted to count the rows in the datatable once the data is loaded  i have seen all the replies which they are misunderstood

 i think datatable does not have the property like gallery has 

[Anonymous]

Yes it's wrong, datatable2 is a Table not a Source. Check the "Data Source" property of datatable2 and write it here instead e.g., 

if (countrows(<copy/paste Data Source property of datatable2 here>) > 0, navigate(blah), false)