Power Apps Data Sources Explained: Tables, Records, and Collections for Absolute Beginners

Introduction

Imagine you're building a simple app for your team to log daily safety inspections at a warehouse. The app needs to display a list of inspection items, let workers mark items as passed or failed, and save those results somewhere. You open Power Apps, drag a few controls onto the screen — and then you hit a wall. Where does the data actually live? How do you get it into the app? What's a "collection," and why does everyone keep mentioning it in tutorials?

This confusion stops more beginners cold than any other part of Power Apps. The visual design clicks quickly. The logic comes with practice. But data — where it lives, how it moves through your app, and what the fundamental structures look like — feels abstract until someone explains it properly. That's exactly what this lesson does.

By the end of this article, you'll understand how Power Apps thinks about data. You'll know what a table is, what a record is, what a field is, and how collections let you work with data inside your app without immediately needing an external database. You'll be able to connect a data source, display records in a gallery, and build a collection from scratch — skills that form the backbone of every canvas app you'll ever build.

What you'll learn:

• What tables, records, and fields are, and how they relate to each other
• The difference between external data sources and in-app collections
• How to connect a SharePoint list or Excel file as a data source
• How to create and manipulate collections using Power Apps formulas
• How to display data from any source in a Gallery control

---

Prerequisites

You should have Power Apps open and be able to create a blank canvas app. If you haven't done that yet, sign in at make.powerapps.com, click + Create, and choose Blank app → Tablet format. You don't need SharePoint or a database set up — we'll work with collections first and connect to external sources later in the lesson.

---

The Three Building Blocks: Fields, Records, and Tables

Before we touch anything in Power Apps, let's build the mental model you'll use for the rest of your career working with data.

Think about a spreadsheet your team uses to track equipment maintenance. Each row is one maintenance event: an asset ID, a technician's name, a date, and a status like "Complete" or "Pending." Each column is a category of information — every row has an entry for each category.

In data terminology:

• A field (also called a column) is a single category of information — like "Technician Name" or "Status."
• A record (also called a row) is one complete entry — all the information about a single maintenance event.
• A table is the whole structure — the collection of all records, organized by fields.

Here's how that maintenance log looks as a table:

| AssetID | TechnicianName | InspectionDate | Status   |
|---------|----------------|----------------|----------|
| EQ-101  | Maria Santos   | 2024-03-15     | Complete |
| EQ-204  | James Okafor   | 2024-03-15     | Pending  |
| EQ-089  | Maria Santos   | 2024-03-16     | Complete |

That table has three records. Each record has four fields. Every data source you connect to in Power Apps — whether it's a SharePoint list, an Excel file, a Dataverse table, or a SQL database — presents its data in this exact structure: a table of records made of fields.

Key insight: Power Apps is designed around tables. Almost everything in the app — galleries, forms, dropdowns — is built to consume a table and display it visually. Once you understand the table/record/field structure, the whole platform starts making sense.

---

Two Categories of Data in Power Apps

Power Apps works with two broad categories of data, and understanding the difference will save you enormous confusion.

External data sources live outside your app. SharePoint lists, Excel files stored in OneDrive, Microsoft Dataverse tables, SQL Server databases — these exist independently of Power Apps. Your app connects to them, reads from them, and writes back to them. The data persists even after you close the app.

Collections live inside your app, in memory, while the app is running. They're temporary tables that you create and manipulate using Power Apps formulas. They're incredibly useful for staging data, handling offline scenarios, or building lists the user can edit before saving everything at once. But when the app closes, a collection disappears unless you've deliberately saved its contents to an external source.

Think of it this way: an external data source is like a filing cabinet in the office — it's always there, everyone can access it, and documents stay in it until someone removes them. A collection is like a whiteboard in a meeting room — you can write things up, rearrange them, erase rows, and work quickly. But when the meeting ends and someone erases the board, that information is gone unless you transferred it to the filing cabinet first.

Most real apps use both. You might load data from SharePoint into a collection, let the user filter and sort it locally (which is fast), then push updates back to SharePoint when the user taps Save.

---

Connecting an External Data Source

Let's make this concrete. We'll connect Power Apps to an Excel file — one of the most beginner-friendly data sources available.

Setting Up the Excel File

Create an Excel file called EquipmentLog.xlsx and add a sheet named Inspections. In that sheet, create the following columns in row 1: AssetID, TechnicianName, InspectionDate, Status. Add a few rows of sample data.

Critical step: Select all your data including the header row, then go to Insert → Table in Excel and check "My table has headers." Name the table Inspections in the Table Design tab. Power Apps requires your Excel data to be formatted as a named table — it cannot read a plain range of cells.

Save this file to your OneDrive for Business account.

Adding the Data Source in Power Apps

In your canvas app, look at the left panel. You'll see icons for the Tree view, Insert, and Data. Click the Data icon — it looks like a small cylinder or database symbol.

At the top of the Data panel, click + Add data. A search box appears. Type OneDrive and select OneDrive for Business. Click Connect, and Power Apps will ask you to sign in or confirm your account.

Once connected, it will show you a file picker. Navigate to EquipmentLog.xlsx and select it. Power Apps will then show the named tables inside that file. Select Inspections and click Connect.

You'll now see Inspections listed in your Data panel. Your app is connected to that Excel table, and you can reference it anywhere in your app using the name Inspections.

Warning: If your Excel data isn't formatted as a named table, Power Apps won't find it. This is the most common reason beginners can't see their data — go back to Excel, select the data, and apply table formatting.

---

Displaying Data in a Gallery

A Gallery control is Power Apps' way of showing a list of records. It repeats a template — a small visual layout — once for each record in the table. It's perfect for our equipment inspection list.

Adding a Gallery

Click Insert in the top toolbar, then choose Gallery → Vertical. A gallery control appears on your screen with some placeholder data. Now look at the formula bar at the top — you'll see the Items property of the gallery is selected, and it currently shows something like CustomGallerySample.

Click inside the formula bar and replace that placeholder with:

Inspections

Just the name of your data source. Press Enter. The gallery will now populate with real records from your Excel file — every row you added will appear as a card in the gallery.

Customizing What the Gallery Shows

Click on the gallery, then click the small pencil icon that appears in the top-left corner of the gallery. This enters edit mode for the template — changes you make here will apply to every record.

By default, the gallery shows a title, subtitle, and body. Click on the title label (the bold text). In the formula bar, you'll see it's bound to ThisItem.Title or something similar. Change it to:

ThisItem.AssetID

Click on the subtitle label and change it to:

ThisItem.TechnicianName

Click somewhere outside the gallery template to exit edit mode. Every card in the gallery now shows the asset ID and technician name from your data. ThisItem is Power Apps' way of saying "the specific record that this particular card is representing right now." It's one of the most important concepts in galleries — each card lives in the context of a single record, and ThisItem.FieldName lets you pull any field from that record.

---

Working With Collections

Now let's build something from scratch using a collection — no external data source required. This teaches you the core formulas you'll use constantly.

ClearCollect: Creating a Collection

The primary formula for creating a collection is ClearCollect. It does two things: clears any existing data in the collection, then fills it with new data. The syntax is:

ClearCollect(CollectionName, Source)

Let's say we want to create a collection of project tasks. We'll trigger this with a button. Insert a Button control and set its OnSelect property to:

ClearCollect(
    ProjectTasks,
    {TaskName: "Review Q1 Reports", AssignedTo: "Priya Mehta", Priority: "High", Done: false},
    {TaskName: "Update Client Database", AssignedTo: "Carlos Rivera", Priority: "Medium", Done: false},
    {TaskName: "Audit Inventory System", AssignedTo: "Priya Mehta", Priority: "High", Done: true},
    {TaskName: "Schedule Team Review", AssignedTo: "James Okafor", Priority: "Low", Done: false}
)

Notice the structure here. Each {...} block is a record literal — you're defining a record by writing each field name, a colon, and a value. Text values go in quotes, numbers are plain numbers, and true/false values (called Boolean values) are written as true or false without quotes.

Click the button in your app (press Alt and click, which simulates a button press in the design environment). Now go to View → Collections in the top menu. You'll see ProjectTasks listed — click it to preview the data. Four records, four fields each.

Now connect a gallery to this collection by setting its Items property to ProjectTasks. Your gallery will show those four tasks. You just created an in-app dataset from nothing.

Collect: Adding Records

ClearCollect wipes and rebuilds. If you want to add a single record to an existing collection, use Collect:

Collect(
    ProjectTasks,
    {TaskName: "Prepare Monthly Dashboard", AssignedTo: "Carlos Rivera", Priority: "Medium", Done: false}
)

Put this on a second button's OnSelect. Every time you press it, a new record gets added to ProjectTasks. Watch the gallery update in real time — this is one of the things that makes Power Apps feel alive.

Patch: Modifying a Record

Patch is the formula you'll use to update an existing record or add a new one. It's slightly more complex, but you'll use it constantly:

Patch(
    ProjectTasks,
    First(Filter(ProjectTasks, TaskName = "Schedule Team Review")),
    {Done: true}
)

Breaking this down:

• ProjectTasks is the collection to modify
• First(Filter(ProjectTasks, TaskName = "Schedule Team Review")) finds the specific record — Filter returns all matching records, First grabs the first one
• {Done: true} is the change to apply — only the Done field is updated, the rest stays the same

Remove: Deleting a Record

To remove a record from a collection:

Remove(ProjectTasks, First(Filter(ProjectTasks, TaskName = "Audit Inventory System")))

In a real gallery, you'd typically use the selected item rather than filtering by name:

Remove(ProjectTasks, Gallery1.Selected)

This removes whatever record the user has clicked in the gallery — a pattern you'll use in almost every app.

Tip: ClearCollect, Collect, Patch, and Remove work with both collections and many external data sources. Learn these four formulas deeply and you can manipulate data almost anywhere in Power Apps.

---

Filtering and Sorting Data

Raw data is rarely what you want to display. Power Apps has two key formulas for shaping data before it reaches your gallery.

Filter returns only the records that match a condition:

Filter(ProjectTasks, Priority = "High")

Set this as a gallery's Items property and the gallery shows only high-priority tasks. You can combine conditions:

Filter(ProjectTasks, Priority = "High" && Done = false)

The && means "and" — only records where priority is High AND Done is false will appear.

SortByColumns orders the records:

SortByColumns(ProjectTasks, "Priority", SortOrder.Ascending)

Combine them for a gallery that shows only incomplete tasks sorted by priority:

SortByColumns(
    Filter(ProjectTasks, Done = false),
    "Priority",
    SortOrder.Ascending
)

This is how real apps work — you build a pipeline of formulas that transforms your raw data into exactly what the user needs to see.

---

Hands-On Exercise

Build this small app from scratch to cement everything you've learned.

Scenario: You're creating a tool to track which office supplies need to be reordered.

1. Create a blank canvas app in tablet format.

2. Add a button labeled Load Supplies. Set its OnSelect to create a collection called SupplyInventory with at least five records. Each record should have these fields: ItemName (text), CurrentStock (number), ReorderThreshold (number), Supplier (text).

3. Add a vertical gallery and set its Items property to:

   Filter(SupplyInventory, CurrentStock < ReorderThreshold)
   

   This shows only items that need reordering.

4. Configure the gallery template so it displays ItemName as the title and Supplier as the subtitle.

5. Add a label below the gallery that displays a count of how many items need reordering:

   "Items to reorder: " & CountRows(Filter(SupplyInventory, CurrentStock < ReorderThreshold))
   

6. Add a button labeled Mark All Ordered. On its OnSelect, use ClearCollect to reload the collection with all CurrentStock values set to a number above the reorder threshold (simulating a restock). Watch the gallery and count label update.

If the gallery empties out after pressing Mark All Ordered and your count label shows 0, you've completed the exercise successfully.

---

Common Mistakes & Troubleshooting

"My gallery is empty even though I connected a data source." Check two things. First, make sure the Items property of the gallery is set to exactly the name of your data source or collection — no extra spaces, correct capitalization. Second, if using Excel, verify the data is formatted as a named Excel table, not just a plain range.

"The fields inside my gallery show the wrong data or nothing at all." When you write ThisItem.FieldName, the field name must exactly match the column name in your data source. If your Excel column is TechnicianName (no space), writing ThisItem.Technician Name will fail silently. Check for spaces, underscores, and capitalization.

"I used Collect to add a record but I'm getting duplicate entries." Collect always adds — it doesn't check for duplicates. If you're pressing a button multiple times, you'll get multiple copies of the same record. Use ClearCollect if you want to reset and rebuild, or add logic to check before collecting.

"My Filter formula returns nothing even though matching records exist." Text comparisons in Power Apps are case-sensitive in some connectors. Also, check that you're comparing the same data types — comparing a text field to a number will silently fail. If your field contains "3" as text and your threshold is the number 3, they won't match.

"I modified a collection but the gallery didn't update." Galleries connected to collections update automatically — but only if the collection itself changed. If you used Patch on an external data source and expected it to update a local collection, you'll need to refresh the collection manually with ClearCollect after the patch.

---

Summary & Next Steps

You've just covered the conceptual and practical foundation of data in Power Apps. Let's recap what you now understand.

Tables, records, and fields are the universal structure for data in Power Apps. Every data source you'll ever work with presents itself as a table of records, each with a consistent set of fields. Understanding this structure lets you work with any connector.

External data sources persist outside your app — Excel, SharePoint, Dataverse, and SQL are the most common for beginners. You connect them through the Data panel and reference them by name anywhere in your app.

Collections are in-memory tables you create and control with formulas. ClearCollect builds them, Collect adds to them, Patch modifies records, and Remove deletes them. They're fast, flexible, and perfect for local work before saving to an external source.

Galleries consume tables and display one card per record. ThisItem.FieldName is how you pull data from the current record into labels and other controls inside the gallery template.

Filter and SortByColumns let you shape data before it reaches the gallery, so users see exactly what they need.

Where to go next:

• Explore connecting Power Apps to a SharePoint list — it's the most common enterprise data source and has some nuances worth learning
• Learn Forms in Power Apps, which give you automatic create/edit/view layouts based on your data source fields
• Dig into Delegation — a critical concept that controls how much of your data Power Apps can actually process for large datasets
• Study Dataverse, Microsoft's purpose-built database for Power Platform apps, which unlocks features that Excel and SharePoint can't provide

The fact that you now understand the table/record/field model, and can manipulate data with formulas, means you have the foundation every more advanced topic builds on. The rest is details.