Skip to main content
Post

How to Load Multiple Data Sources into ONE Collection

How to Load Multiple Data Sources into ONE Collection

Data Collections

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 with ShowColumns() and RenameColumns() and mark the origin of each row with AddColumns().

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.

🔸 #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).

This post is licensed under CC BY 4.0 by the author.

Microsoft and the Power Platform product names (for example Power Apps, Power Automate, Copilot Studio) are trademarks of the Microsoft group of companies. This is an independent website. It is not operated, sponsored or endorsed by Microsoft.