Connect to data sources by using connectors

One advantage of building canvas applications using Power Apps is the ability to connect that application to hundreds of different data sources. For example, a retail company might design a greeter application that employees use to assist customers as they enter the store. Through a single canvas application, the greeter would be able to access customer information from their Point of Sale (POS) systems, Customer Relation Management (CRM) systems, and data in SharePoint.
When you're learning to use Power Apps, there are several data sources options you can use that are available to you at no extra cost. You can connect to hundreds of different data sources, but some require extra licensing. In this unit, we discuss some of the more popular options.

When you're learning to use Power Apps, there are multiple data sources options you can use that are available to you at no extra cost. In this unit, we discuss a few of the more popular options.

Add external data connections to canvas apps in Power Apps

When you build an app, you can start by connecting to an external data set. This connection is done by using standard and premium connectors. While Power Platform has many options available to connect to, there are several that are often used, SharePoint, Excel, SQL Server, and Microsoft Dataverse.

Let's look at SharePoint first.

Sharepoint

SharePoint is a popular application for collaboration and document management to create lists and document libraries which serve as the backend data sources for your app. A SharePoint list functions like a table of data, while a document library can store files or read spreadsheet table data, similar to OneDrive. You can connect to SharePoint using the SharePoint connector. SharePoint lists can range from simple single-column lists, such as dropdown options, to multi-column lists with more complex data structures. Importantly, there’s no limit to the number of SharePoint lists or document libraries you can connect to your app.

When working with SharePoint as a data source, keep the following best practices in mind:

• Simplify Column Types: Use basic types like Text, Number, Yes/No, or Date and Time. Avoid complex column types unless necessary, as your app can manage sophisticated logic and write data to simple columns.
• Avoid Mandatory Columns: Instead of making columns required in SharePoint, enforce mandatory inputs directly within your app.
• Use Simple Column Names: Stick to straightforward names without special characters or spaces. You can rename columns inside your app for clarity while keeping them easy to reference in SharePoint.
• Manually Relate Tables: SharePoint lists don’t inherently relate to one another like a database. If relationships are needed, create "key" fields to link tables manually.

SharePoint has limitations when querying large datasets due to delegation rules. For example, if a list contains more than 100 items, SharePoint paginates the data, loading it in chunks. In Power Apps, queries that exceed the delegation limit may result in incomplete data, indicated by a yellow warning triangle on your controls. To avoid this outcome, design queries that stay within delegation limits or optimize your data structure.

By following these guidelines, you can effectively use SharePoint as a data source while minimizing common pitfalls and ensuring smooth integration with your app.

Excel

Excel is another popular nonpremium data source to consider. Excel data can also be imported into a SharePoint list or a Dataverse table. Important considerations for using Excel as a direct data source for your app are as follows:

Any data you use must be formatted as a table. You can only accomplish this inside of Excel. If you don't have your data set up as a table, your app can't see the data.

When you build a gallery from an Excel table, the gallery only uses images that have "[image]" in the column header. It autopopulates your gallery fields in alpha-numeric order. So, you might need to update the field Item properties to get the desired column from your data.

If someone has the Excel document open to modify data, it hinders read/write of that data from within the app. So, an Excel table, unless you're reading static data, isn't a preferred choice when you have multiple app users.

Overall, Excel is a good data source you can use for your app without paying extra license fees, but it should be considered behind SharePoint.

SQL

Though considered a "premium" data source, SQL is another excellent choice for apps requiring large databases. Many organizations already keep data in SQL, and SQL tables can easily be connected to your app. If your SQL resides in the cloud, such as Microsoft SQL Server, then your data connection is straightforward. It's even possible to use locally stored SQL data by setting up an on-premises Data Gateway. Since Power Apps resides in the cloud, it needs a connection that is based in the cloud to be able to access your locally stored data. But it can be securely established to protect your data. For large databases, SQL is another excellent choice as a data source.

Microsoft Dataverse

When building an app from Dataverse, you don't need to create a connection from Power Apps, unlike with data sources such as SharePoint, or Excel. Power Apps makes it easy to create any data table to meet your needs, and it's easy to import existing data into a Dataverse table. When you retrieve data, there are no API calls required, your app simply accesses the data within your Environment. Dataverse can hold large amounts of data, and you can even expand the space, with more licensing, to suit your organizational needs. Dataverse can also create relationships automatically between tables, enabling you to pull data from multiple tables in a single gallery without costly lookups to other tables. It's fast, comprehensive, and interfaces perfectly with Power Apps.

Another advantage of using Dataverse is the ability to use the Power Apps Copilot feature to describe what you want your app to do in everyday words. Any of the options available from the Power Apps Maker Portal where you Start with data are designed to work with Dataverse. The full functionality of Power Apps generative AI feature is available when you're using Dataverse.

Choosing the right data source for your app

Selecting the most suitable data source for your app is a critical step in ensuring it meets your requirements for performance, scalability, and functionality. The choice often depends on factors such as the size and complexity of your app, licensing considerations, and how users interact with the data.

• Small-scale apps
  If your app is relatively simple and doesn’t require handling large datasets or complex relationships, SharePoint or Excel can be excellent choices. They're cost-effective, easy to set up, and work well for lightweight applications. However, keep in mind that these options may have limitations when it comes to manage large volumes of data or supporting multiple simultaneous users. For example, a team task tracker app could use a SharePoint list to store task details, or an Excel table in OneDrive to manage a small inventory list.

• Enterprise-level apps
  For more complex or large-scale applications, Dataverse or SQL Server are better suited. These data sources offer advanced features like built-in table relationships, robust security, and high scalability, making them ideal for enterprise scenarios. While they may require extra licensing, their performance and flexibility make them worth the investment for apps that need to manage significant amounts of data or support many users. A customer relationship management (CRM) app, for instance, could use Dataverse to store customer profiles, purchase history, and interactions. Meanwhile, a financial reporting app might use SQL Server to process and analyze large datasets.

• Consider user access and concurrency
  Another factor to consider is how many users access the app and how often. Some data sources, like Excel, may struggle with concurrent edits by multiple users, while others, like Dataverse or SQL Server, are designed to handle high levels of simultaneous activity without performance degradation. Always ensure the data source you choose aligns with the expected usage patterns of your app.

By carefully evaluating the strengths and limitations of each data source, you can make informed decisions that align with your app’s goals. Whether you’re building a simple tool for a small team or a robust solution for an entire organization, choosing the right data source ensures your app performs reliably and delivers a seamless user experience.