In this article, we will learn about Power Apps Data sources and The items limitation and how to overcome this limitation.
Power Apps Data Sources
Most of the Canvas apps that you will create in Power Apps, need external information, this information is what we called Power Apps Data Source. You can add as many data sources as you want to one application.
Power Apps can’t be used to create a connected data sources, or modify their structure, so these data sources must already exist in service somewhere. However, collection data sources can be created and modified inside an app, but they are only temporary.
The Model-driven apps and the portal work with Data Verse.
These are some of the popular data sources that you can use inside your app,
- Excel, you can use an excel table as a Power Apps data source.
- Data Verse “Common data service” is an option of Power Apps data sources that we can use in our app to Store data for business applications. The Data Verse Stores data within a set of tables similar to data in the table in the database, Data Verse is Secure data, this means that only users who have access to the tables can see them.
- Also, SharePoint is considered one of Power Apps data sources. you can get your data from SharePoint site lists and libraries.
- Also, SQL Server, Oracle, and Dynamic 365 all are types of Power Apps data sources, And you can add many data sources to one app.
Types of Power Apps Data sources
There are two types of Power Apps data sources. The Data sources can be connected to a cloud service, or they can be local to an app.
The data source is loaded from the service automatically when the app is loaded.
If you want to force the data to refresh, you can use the Refresh function.
Power Apps Connected Data sources
Which backed by a connection to a service in the cloud. There are two types depending on the returned data from these data sources as the following:
- Tables, the most common data sources
- You can use Connectors to read and write data to this type of data source. Within a connection, you can access multiple tables of information, and each table will become a separate data source.
- The connected data source such as Excel, SharePoint lists, and SQL tables that return tables.
- There are many formats that can be stored in the cloud service such as OneDrive for Business, DropBox, and SQL Server, etc
Power Apps will replace the spaces in the column names in SharePoint and Excel data sources with “_x0020_” For example, “First Name” in SharePoint or Excel will appear as “First_x0020_Name” in Power Apps when displayed in the data layout or used in a formula.
- Not Tables
- Include email, calendars, Twitter, and notifications.
Local Data sources
The local data sources is another type of Power Apps Data sources which is ,
- Local to the app.
- Not backed by a connection to a service in the cloud.
- Such as Collections, that can be loaded and saved locally.
- The information in the Local data sources is stored in a private location that other users, apps, or devices can’t access.
What is the Power Apps Data source limit?
If the data in your Power Apps data sources exceeds 500 records and any part of function you used in a formula can’t be delegated, Power Apps might not be able to retrieve all of your data, and the app may have wrong results. It will only get the first 500 records.
For example, if your Data source is an excel file with 700 rows, and you want to get the last 10 rows, in this case you will get the you will get rows 490-500 instead of 690-700.
How can we overcome the 500 items limitation in Power Apps?
To work around this, we have many options,
- Increase the total limit of items you can fetch
- On the File tab, select App settings
- Advanced Settings, change the Data row limit and increase the value of “500 items”. but 2000 is the maximum value you can set, which means you can’t get more than 2000 items.
- You can use static data
- you can use delegation
- You can use flow to get all the rows
Most of the canvas apps based on data sources, these data sources must already exist in service somewhere as Power Apps can’t be used to create a connected data source, or modify its structure, you can add many data sources to your app. Model-driven apps and Portals need Data verse.