OVERCOME 2000 ITEMS LIMIT USING POWER APPS COLLECT FUNCTION

BASICS

For some of use it is a real surprise when we learn, that functions Collect and ClearCollect are actually… non-delegable. So when using a Collect(datasource) function, the maximum number of returned rows is limited by the data row limit setting:

By default, this is set to 500 and you can increase it to max. 2000.

In my case, the reason why I needed to load more data to a collection, was that later in the application I had to do complex filtering operations, which were non-delegable as well, so in the end I would never receive the data I was expecting.

PREREQUISITES

The only downside of my solution, that is caused by SharePoint, is the fact, that column ID cannot be used for range comparisons (higher, lower, etc…) because using it for such makes the whole query non-delegable (source). If you are using other sources such as CDS or SQL Server, that shouldn’t be an issue.

So for SharePoint you need to have another column, that is the number type and that preferably has the same values as the corresponding ID column values.

Unfortunately too, it is not possible to use calculated column for this purpose as even if it’s set to “Number” Power Apps still recognizes it as text and doesn’t allow to use in range comparisons.

SO HOW TO DO IT?

First you need to calculate number of iterations, that will be required to download all data from data source. To do that, I am taking ID values of the first record and the last. Next, I divide their difference by the number of rows returned by single iteration. This number cannot be bigger than a set row data limit of course 

Set(

firstRecord,

First('Large List')

);

Set(

lastRecord,

First(

Sort(

'Large List',

ID,

Descending

)

)

);

Set(

iterationsNo,

RoundUp(

(lastRecord.ID - firstRecord.ID) / 500,

0

)

);

Collect(iterations, Sequence(iterationsNo,0));

Last step is to create collection, where each item is a number of an iteration. So eg. if I have 1000 records and want to get them in 500 per each iteration, I will have two iterations, so the iterations collection will contain: [0, 1] .

Next for each iteration, I am calculating the lower and upper boundary, to download items with IDs between. So eg. for first iteration I will need to get items having ID between 0 and 500:

ForAll(

iterations,

With(

{

prevThreshold: Value(Value) * 500,

nextThreshold: (Value(Value) + 1) * 500

},

If(

lastRecord.ID > Value,

Collect(

LargeListSP,

Filter(

'Large List',

ID_val > prevThreshold && ID_val <= nextThreshold

)

)

)

)

);

The Filter expression using Numeric column for range comparisons is delegable, so it doesn’t cause any issues  And that’s it. By following this pattern you are able to download in batches thousands of items from any data source. The value of ID_val in my case is just that additional column, with values like the corresponding values in the ID column.

This is how it looks in action:

No, I have no idea why rows count shows 3 items less than actually is, but well… Nothing is perfect 

References :

https://poszytek.eu/en/microsoft-en/office-365-en/powerapps-en/overcome-2000-items-limit-using-power-apps-collect-function/

DA-100-Analyzing-Data-with-Power-BI

Complete Lab Data :

https://github.com/MicrosoftLearning/DA-100-Analyzing-Data-with-Power-BI/tree/master/Instructions/Labs

Separate Links:

DA-100 Prepare data for analysis

https://docs.microsoft.com/en-us/learn/modules/get-data/

DA-100 Clean, Transform, and load data in Power BI

https://docs.microsoft.com/en-us/learn/modules/clean-data-power-bi/

DA-100 Clean, transform, and load data in Power BI

https://docs.microsoft.com/en-us/learn/modules/clean-data-power-bi/

DA-100 Design a data model in Power BI

https://docs.microsoft.com/en-us/learn/modules/design-model-power-bi/

https://docs.microsoft.com/en-us/power-bi/guidance/relationships-active-inactive

DA-100 Introduction to creating measures using DAX in Power BI

https://docs.microsoft.com/en-us/learn/modules/create-measures-dax-power-bi/

DA-100 Work with Power BI visuals

https://docs.microsoft.com/en-us/learn/modules/visuals-power-bi/

DA-100 Create a data-drive story with Power BI

https://docs.microsoft.com/en-us/learn/modules/data-driven-story-power-bi/

DA-100 Create Paginated Reports

https://docs.microsoft.com/en-us/learn/modules/create-paginated-reports-power-bi/

DA-100 Optimize a model for performance in Power BI

https://docs.microsoft.com/en-us/learn/modules/create-measures-dax-power-bi/

DA-100 Perform analytics in Power BI

https://docs.microsoft.com/en-us/learn/paths/perform-analytics-power-bi/

Work with AI visuals

https://docs.microsoft.com/en-us/learn/modules/ai-visuals-power-bi/

DA-100 Manage datasets in Power BI

https://docs.microsoft.com/en-us/learn/modules/manage-datasets-power-bi/

DA-100 Implement row-level security

https://docs.microsoft.com/en-us/learn/modules/row-level-security-power-bi/

https://docs.microsoft.com/en-us/learn/modules/create-manage-workspaces-power-bi/

Data linage access control:

https://docs.microsoft.com/en-us/learn/modules/create-manage-workspaces-power-bi/

Mange the Apps:

https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-create-distribute-apps

Managing workspace:

https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-modern-usage-metrics

Power BI Workspace Permissions and Roles

 https://www.mssqltips.com/sqlservertip/6487/power-bi-workspace-permissions-and-roles/

 

Trainer Email ID && Linkedin

sharatc@cloudthat.com, abhiit.abhishek@gmail.com

www.linkedin.com/in/abhiitmct

How to overcome PowerApps Delegation limit?

 In this article we will discuss PowerApps delegation, Delegable data sources and functions, and hw to overcome the delegation warning in PowerApps?

Table of Contents

1. 1 What is PowerApps Delegation?
2. 2 PowerApps Delegation limit
3. 3 PowerApps Delegable data sources
4. 4 Delegable Functions
5. 5 How to overcome PowerApps delegation warning?

What is PowerApps Delegation?

Delegation is very important concept that you have to understand, specially when you will work with large data sets in PowerApps. First let’s know that delegate means to assign tasks to someone else.

PowerApps Delegation means when you work with the different data sources such as dataverse, SharePoint SQL Server ,etc, the data processing are assigned to data sources rather than done by PowerApps itself.This means Power Apps will retrieve small amount of data  and this will speed the app and improving the user experience.

PowerApps Delegation limit

The default delegation limit in PowerApps is 500 records. Delegation does not apply to data sets with less than 500 records, they will work correctly without any issue. Also you can increase this limit to be up to 2000 records.

• Open your app
• Go to File Tab >> App settings
• Click on Advanced Settings.
• You can set the value: “the Data row limit for non-delegable queries” to be up to 2000 records.

Change PowerApps delegation limit

But if your data sets exceed the limit that defined in your app, your app will work correctly with the first 500 records “Assume that the defined limit is 500”. but it will not work correctly with other recordes.

Ex: if you have 600 records in your data set and you want to retrieve the last 10 records, in this case it will return the record from 490 to 500 in stead of returning the correct records that from 590 to 600. So its important to understand the delegation if you want your app to work correclty.

PowerApps Delegable data sources

As we know there are many data sources that are supported to work with PowerApps , but not all of them are delegable, you will find few data sources that support delegation in PowerApps.These data sources support delegation process the data quit similar to what PowerApps do.

Some of the popular delegable data sources are:

• Dataverse ,
• SharePoint,
• SQL Server,
• Dynamics 365 (CRM)
• etc.

Unfortunately Excel data source not delegable, but it depends whether your excel file stored in the cloud or stored locally in the app.

If you stored your excel file in the OneDrive for Bussines for example, then you have to fetch the data source through the network traffic, in this case your Excel data source not supporting delegtaion.

But when you store your excel file locally in the app as a static data source, then you will not facing any PowerApps delegation problems, as there is no network traffic and your data is locally stored in the app itself.

When performing operations, like filtering or searching for specific records in PowerApps, they performed at the data source instead of bringing the whole set of data to the app and then processing it locally, so PowerApps delegation assign the work to the data source instead of the PowerApps itself.

Although you use delegable data sources,you get delegation warning, That’s because the delegation not only depends on the data sources but also it depends on the functions that you use in your formulas.

Delegable Functions

As there are delegable and non-delegable data sources, there are delegable and non-delegable functions.

For Example, Filter, Search,,LookUp, Sort, SortByColumns, Sum, Average, Min, and Max functions can be delegated.

AddColumns, DropColumns, RenameColumns, and ShowColumns partially support delegation.

For more details you can check Understand delegation in a canvas app.

How to overcome PowerApps delegation warning?

PowerApps delegation warning

You will receive delegation warning if you use a function that is not yet supported by the data source.

You will see a blue line under parts of the code along with a warning triangle.

To Avoid the delegation warning,

• Be aware of which functions and operatives can be delegated back to the data source and how delegation works, so try to use all the possible Delegable functions.
• You can Change the 500 limit value up to 2000 as we discussed at the beginning of this article in the PowerApps Delegation limit section.
• If you have a large dataset, You can create some Static views instead of dealing with the whole data. For example, When using Dataverse table as a data source you can select a view instead of using the whole data source.

Use Views to avoid PowerApps Delegation

Conclusion

To Avoid the Delegation warning in PowerApps try wisely to use your data source, You can use SQL server, Dataverse or SharePoint, and you can increase the rows of your data by increasing the value above than 500 record.