How to Load Multiple Data Sources into ONE Collection
TL;DR:
ClearCollect(colOrders, SourceA, SourceB)puts the rows of several tables into one collection. Columns that exist in only one source are blank for the rows of the other source. Align the schemas withShowColumns()andRenameColumns()and mark the origin of each row withAddColumns().
Often, data is not stored in just one place, but you need a combination of multiple data sources. While you could solve this with multiple collections, this is not always the most practical approach.
💡 Challenge
A gallery, a chart or an export should show rows from several tables at once: this year’s orders and last year’s, a live list and an archive. The tables rarely match column for column. One calls the customer Customer, the other Client, and one has an extra column the other does not know.
✅ Solution
ClearCollect() accepts any number of records and tables after the collection name and adds all their rows to one collection. For a collection, Power Apps creates additional columns as needed, so differing columns never cause an error. You decide how the columns line up: rename with RenameColumns(), drop with ShowColumns(), add a marker with AddColumns().
🔧 How It’s Done
The examples below use three small tables, so you can reproduce every result yourself. Run them in a behavior formula, for example the OnSelect of a button:
Set(Orders2024, Table(
{ID: 1, Customer: "Contoso", Amount: 100},
{ID: 2, Customer: "Fabrikam", Amount: 200}
));
Set(Orders2025, Table(
{ID: 3, Customer: "Adatum", Amount: 300},
{ID: 4, Customer: "Tailwind", Amount: 400}
));
Set(Archive, Table(
{ID: 5, Client: "Litware", Amount: 500, Region: "EU"}
))
Orders2024 and Orders2025 have identical columns. Archive has no Customer column, calls the same information Client, and has an additional Region column.
1. Combine two identical data sources
When both sources have the same columns:
ClearCollect(colOrders, Orders2024, Orders2025)
Result: one collection with all four rows.
| Amount | Customer | ID |
|---|---|---|
| 100 | Contoso | 1 |
| 200 | Fabrikam | 2 |
| 300 | Adatum | 3 |
| 400 | Tailwind | 4 |
2. Add a source column
To see later where a row came from, add a marker column to each source before combining:
ClearCollect(
colOrders,
AddColumns(Orders2024, Source, "2024"),
AddColumns(Orders2025, Source, "2025")
)
Result:
| Amount | Customer | ID | Source |
|---|---|---|---|
| 100 | Contoso | 1 | 2024 |
| 200 | Fabrikam | 2 | 2024 |
| 300 | Adatum | 3 | 2025 |
| 400 | Tailwind | 4 | 2025 |
3. Combine sources with different columns
The columns do not have to match. If you combine Orders2024 and Archive as they are, the result contains every column of both tables, and each row is blank where its source had no such column:
ClearCollect(colOrders, Orders2024, Archive)
| Amount | Client | Customer | ID | Region |
|---|---|---|---|---|
| 100 | Contoso | 1 | ||
| 200 | Fabrikam | 2 | ||
| 500 | Litware | 5 | EU |
There is no error, but Customer is blank for the archive row and Client is blank for the two orders. That is rarely what you want. Align the schemas first.
4. Rename a column with RenameColumns
ClearCollect(
colOrders,
Orders2024,
RenameColumns(Archive, Client, Customer)
)
Client becomes Customer, so the names end up in one column. The additional Region column remains and is blank for the 2024 rows:
| Amount | Customer | ID | Region |
|---|---|---|---|
| 100 | Contoso | 1 | |
| 200 | Fabrikam | 2 | |
| 500 | Litware | 5 | EU |
5. Keep only the shared columns with ShowColumns
ClearCollect(
colOrders,
Orders2024,
RenameColumns(
ShowColumns(Archive, ID, Client, Amount),
Client, Customer
)
)
Result: exactly the three columns of Orders2024, Region is gone.
| Amount | Customer | ID |
|---|---|---|
| 100 | Contoso | 1 |
| 200 | Fabrikam | 2 |
| 500 | Litware | 5 |
If you keep only columns that the other table does not have under the same name, for example ShowColumns(Archive, ID, Amount) without renaming Client, the Customer column is blank for the archive row:
| Amount | Customer | ID |
|---|---|---|
| 100 | Contoso | 1 |
| 200 | Fabrikam | 2 |
| 500 | 5 |
6. Add data from another data source (lookup)
To enrich each row with a value from a related table, use AddColumns() with LookUp(). The example adds a Segment from a Customers table to every order:
Set(Customers, Table(
{ID: 1, Segment: "Retail"},
{ID: 3, Segment: "Wholesale"}
));
ClearCollect(
colOrders,
AddColumns(
Orders2024 As O,
Segment,
LookUp(Customers As C, C.ID = O.ID).Segment
)
)
Result:
| Amount | Customer | ID | Segment |
|---|---|---|---|
| 100 | Contoso | 1 | Retail |
| 200 | Fabrikam | 2 |
The order with ID 2 has no match in Customers, so Segment is blank for it. Naming both tables with As keeps it clear which ID is meant.
🎉 Result
One collection holds the rows of all sources with one consistent set of columns. Galleries, charts and exports work against a single table, and the Source column still tells you where each row came from.
🌟 Key Advantages
🔸 One formula: ClearCollect(col, SourceA, SourceB) replaces several collections.
🔸 No schema errors: columns that only one source has are blank for the rows of the other source.
🔸 Full control: RenameColumns() merges differently named columns, ShowColumns() keeps only what you need.
🔸 Traceable: a constant AddColumns() marker shows the origin of each row.
⚠️ Good to Know
ClearCollect() is not delegable. With a real data source such as SharePoint or Dataverse, each source delivers only up to the app’s Data row limit: 500 rows by default, adjustable up to 10,000 in Settings > General. With two data sources you get at most twice that number into the collection. Filter each source with a delegable Filter() first, so only the rows you need count against the limit.
🛠️ FAQ
1. Do the column names have to match exactly?
No. Columns that exist in only one source are kept and are blank for the rows of the other source. To get one column instead of two, rename with RenameColumns().
2. How do I load only some columns?
Wrap the source in ShowColumns() and list the columns you need, for example ShowColumns(Archive, ID, Amount).
3. How do I know which source a row came from?
Add a marker column with AddColumns(Orders2024, Source, "2024") before combining, as shown in step 2.
4. What happens with rows that have no match in the lookup?
The new column is blank for those rows.
5. How many rows can I load?
At most the Data row limit of the app per data source: 500 by default, up to 10,000 if you raise it in the app settings. Larger limits make the app slower, so filter the sources first.
🔗 Related Tips
🔸 #PowerPlatformTip 113: ‘Combine Multiple Data Sources’ - a related approach, ungrouping mixed static and dynamic sources into one flat table.
🔸 #PowerPlatformTip 16: ‘PowerApps: Collections Cookbook’ - more copy-paste recipes for manipulating the resulting collection.
🎥 Video Tutorial
This tip is by Marcel Lehmann, Microsoft MVP (Business Applications) and founder of the Power Platform Academy, which runs free live workshops (in German). See the next free workshop dates (in German). For companies that want this implemented for them, see KMUpower: Power Platform consulting & automation for SMEs (DACH).
