Power Apps Working with Data Sources and Connectors

A Power App without data is just a collection of pretty buttons. Data transforms an app into a working tool that reads from real systems, writes back to them, and keeps everything synchronized. This topic explains how Power Apps connects to the outside world through connectors, what options you have for storing data, and how to choose and use the right data source for each situation.

What Is a Connector?

A connector is a pre-built bridge between Power Apps and an external service or database. Microsoft has built over 1,000 connectors — covering everything from SharePoint and Excel to Salesforce, SAP, Google Drive, Twitter, and even niche industry-specific services. Instead of writing code to call an API, you just add a connector, sign in, and start reading and writing data.

The Connector Plumbing Diagram

CONNECTOR ARCHITECTURE

Your Canvas App
      │
      │ (calls)
      ▼
 CONNECTOR LAYER (Microsoft managed)
  ┌────────────────────────────────────────────────────┐
  │  SharePoint  │  Excel  │  SQL Server  │  Dataverse │
  │  Connector   │Connector│  Connector   │  Connector │
  └──────┬───────┴────┬────┴──────┬───────┴─────┬──────┘
         │            │           │             │
         ▼            ▼           ▼             ▼
    SharePoint     Excel       SQL Server    Dataverse
    Online         Online       Database      Tables
    Lists          Files

The connector handles all the authentication, data translation, and communication complexity. Your formulas call the connector using simple Power Fx syntax — you never write a line of API code.

Standard vs. Premium Connectors

Connectors are divided into two categories that directly affect what license you need.

CONNECTOR CATEGORIES

STANDARD CONNECTORS (free with Microsoft 365):
  ✓ SharePoint         ✓ Excel Online (Business)
  ✓ Outlook           ✓ OneDrive for Business
  ✓ Teams             ✓ Forms
  ✓ Planner           ✓ Office 365 Users
  ✓ Azure AD          ✓ Notifications
  (and ~200 more)

PREMIUM CONNECTORS (require paid Power Platform license):
  ✓ Dataverse          ✓ SQL Server
  ✓ Salesforce         ✓ SAP
  ✓ DocuSign           ✓ ServiceNow
  ✓ Stripe             ✓ Zendesk
  ✓ Adobe Sign         ✓ Twilio (SMS)
  (and 800+ more)

CUSTOM CONNECTORS (you build these yourself — Topic 19):
  ✓ Any REST API with OpenAPI/Swagger spec
  ✓ Your own internal systems
  ✓ Third-party APIs not covered by standard connectors

Adding a Data Connection to Your App

In Power Apps Studio, click the Data icon on the left sidebar (it looks like a cylinder). Click Add data. A search panel opens. Type the name of the service you want (SharePoint, Dataverse, SQL, etc.). Click the connector, then authenticate — sign in with your Microsoft account for Microsoft services, or provide API keys or credentials for external services. Once connected, the data source appears in your Data panel and you can reference it in formulas.

Reference the Data Source in Formulas

Once added, you reference the data source by name in formulas. If you added a SharePoint list called "Employees," you write Filter(Employees, Department = "Sales") in a gallery's Items property. Power Apps knows "Employees" is a SharePoint list and queries it through the SharePoint connector automatically.

SharePoint Lists as a Data Source

SharePoint lists are the most common data source for Power Apps among organizations that already use Microsoft 365. They require no premium license, sit inside familiar SharePoint sites, and support most standard data operations.

What SharePoint Lists Are Good For

Use SharePoint lists for departmental apps that team members already access through SharePoint, simple approval tracking, event registrations, feedback forms, and any list-based data that does not need complex relationships or large-scale querying.

SharePoint List Limitations in Power Apps

SharePoint lists hit performance issues above 5,000 rows in Power Apps because Power Apps can only delegate certain operations to SharePoint (Filter and Sort on indexed columns work; complex combined conditions often do not). If your app needs to filter by multiple conditions on thousands of records, consider migrating to Dataverse or SQL Server.

Connecting to SharePoint

Steps:
1. Add data → SharePoint
2. Enter your SharePoint site URL: https://yourcompany.sharepoint.com/sites/HR
3. Select the list: "Employee Records"
4. Click Connect

Use in Gallery Items:
Filter( 'Employee Records', Department = DeptDrop.Selected.Value )

Create new record:
Patch( 'Employee Records',
       Defaults('Employee Records'),
       { Title: NameInput.Text, Department: DeptDrop.Selected.Value }
)

Excel Online as a Data Source

Excel files stored in OneDrive for Business or SharePoint can serve as data sources. The file must be formatted as an Excel Table (Insert → Table in Excel). Power Apps reads and writes rows through the Excel connector.

Important Excel Limitations

Excel as a Power Apps data source is not delegable — Power Apps downloads the entire table into memory and filters locally, with a default 500-row limit (adjustable to 2,000 in Settings). Use Excel only for small reference tables — lookup lists, configuration values, or demonstration data. For real business apps handling more than a few hundred rows, use Dataverse or SharePoint.

SQL Server as a Data Source

Many organizations store critical business data in SQL Server databases. Power Apps connects to both on-premises SQL Server (through an on-premises data gateway) and SQL Server in Azure (directly through the internet).

On-Premises Data Gateway

The on-premises data gateway is a small software agent installed on a server inside your corporate network. It creates a secure tunnel between Power Platform in the cloud and your SQL Server on-premises. Install the gateway from the Power Platform Admin Center, configure it with your server credentials, and then Power Apps routes SQL queries through it automatically. The gateway handles all encryption and routing — you see no difference in how you write formulas.

ON-PREMISES GATEWAY DIAGRAM

Power Apps (cloud)
       │
       │ encrypted tunnel
       ▼
On-Premises Data Gateway
(installed on your server)
       │
       │ internal network
       ▼
SQL Server Database
(behind corporate firewall)

Using SQL Server in Formulas

SQL Server tables appear like any other data source in Power Apps. The difference is that most SQL Server operations are delegable — Filter, Sort, Search, and aggregate functions work on the server side, handling millions of rows efficiently. This makes SQL Server an excellent choice for large-scale enterprise apps.

Dataverse as a Data Source (Recommended for Production Apps)

Dataverse is the recommended data source for any serious Power Apps solution. It supports full delegation, fine-grained security, business rules, and relationships between tables — all of which make production apps reliable and scalable.

Dataverse-Specific Capabilities in Power Apps

When you use Dataverse as your data source, several extra features activate automatically. Offline capability becomes available — Power Apps can cache Dataverse data on the device and sync when the connection restores. The User() function integrates with Dataverse to track record owners automatically. File and Image columns in Dataverse stream binary content directly to Image controls without base64 encoding. Relationships between tables can be traversed directly in formulas without writing joins.

ACCESSING RELATED TABLE DATA IN DATAVERSE

Orders table has a Customer lookup column.
Each order points to one customer record.

In a Gallery showing Orders, show the Customer's name:

Gallery Items: Orders
Title label Text: ThisItem.Customer.FullName  // traverse the relationship!

No join formula needed — Dataverse handles the relationship automatically.

HTTP Request Connector and HTTP with Azure AD

Many organizations have internal APIs that do not have a pre-built Power Apps connector. The HTTP connector lets Power Apps call any REST API by specifying the URL, method (GET, POST, PUT, DELETE), headers, and body directly in formulas.

HTTP CONNECTOR FORMULA EXAMPLE

Office365Outlook.SendEmail(
    "recipient@company.com",
    "Your Request Has Been Approved",
    "Hi, your service request ID " & ThisItem.RequestID & " is approved."
)

Or calling a custom REST API:
Set( APIResult,
    'HTTP-with-Azure-AD'.Get(
        "https://api.yourcompany.com/employees/" & EmpID
    )
)

The Office 365 Users Connector: Knowing Who Is Logged In

The Office 365 Users connector lets your app look up information about the current user or any user in your organization's directory — their name, email, department, manager, photo, and more. This connector is standard (free) and extremely useful for personalization.

COMMON OFFICE 365 USERS FORMULAS

User().FullName        → "Priya Sharma"         (logged-in user's name)
User().Email           → "priya@company.com"    (logged-in user's email)

Office365Users.MyProfile().DisplayName      → Full display name
Office365Users.MyProfile().Department       → User's department
Office365Users.MyProfile().OfficePhone      → Phone number
Office365Users.MyProfile().Photo            → Profile photo (for Image control)

Look up another user:
Office365Users.UserProfile("james@company.com").Department

Show who a person reports to:
Office365Users.Manager("priya@company.com").DisplayName

The Microsoft Forms Connector

If your organization collects data through Microsoft Forms, Power Automate (not Power Apps directly) is the standard tool for reading Forms responses — triggered when a new response is submitted. However, Power Apps can read completed Forms responses through the Microsoft Forms connector to display summary statistics or search through submissions.

Choosing the Right Data Source

DATA SOURCE DECISION TREE

Does your app need premium features (security roles, relationships)?
  YES → Use Dataverse
  NO ↓

Do you have existing SharePoint lists your team already uses?
  YES → Use SharePoint Lists
  NO ↓

Do you have an existing SQL Server database?
  YES → Use SQL Server (with gateway if on-premises)
  NO ↓

Is this a prototype or very simple app with under 200 rows?
  YES → Excel Online works fine
  NO → Create a Dataverse table (sign up for Developer Plan)

Handling Connections and Authentication

Power Apps connections use the logged-in user's credentials by default — this is called "user authentication." When Priya opens your app, the app connects to SharePoint using Priya's SharePoint permissions. If Priya does not have access to a SharePoint list, the app cannot read that list. This is actually a security feature — it prevents apps from bypassing permission systems.

Service Principals for Automated Scenarios

Sometimes you need an app to connect to a data source using a service account rather than the user's own credentials — for example, when writing to a central log table that users themselves should not have direct write access to. You accomplish this through Power Automate: the app calls a flow, the flow uses its own connection (service account credentials), and the flow writes to the restricted data source. The app itself never gets direct access. This pattern keeps security tight while enabling flexible scenarios.

Refreshing Data in Your App

Power Apps caches data locally for performance. If another user changes data in your data source while the current user has the app open, the app does not automatically update. Use the Refresh function to force a fresh data load.

Refresh( 'Service Requests' )
→ Reloads all Service Request data from the source

Put this on a "Refresh" button:
OnSelect = Refresh( 'Service Requests' )

Or add it to a Timer control that fires every 60 seconds:
Timer1.OnTimerEnd = Refresh( 'Service Requests' )

Key Points

  • Connectors are pre-built bridges to over 1,000 external services. Standard connectors are free with Microsoft 365. Premium connectors require a paid Power Platform license.
  • Add data sources in Power Apps Studio through the Data panel. Authenticate once — Power Apps uses your credentials for all future queries in that session.
  • SharePoint lists work well for small-team apps without complex relationships. Excel works only for small reference tables. SQL Server handles large datasets with full delegation. Dataverse is the recommended choice for production apps.
  • The on-premises data gateway lets Power Apps reach SQL Server or other databases inside your corporate network without opening firewall ports.
  • Use the Office 365 Users connector to personalize apps with the current user's name, photo, department, and manager.
  • User authentication means the app uses the logged-in user's permissions — apps cannot access data the user does not have permission for. Use Power Automate flows to write to restricted data sources using service account credentials.
  • Use the Refresh function on a button or timer to reload data when it may have changed since the app last loaded it.
Previous lessons
Back to courses
Next lessons

Leave a Comment