#PowerPlatformTip 113: 'Combine Multiple Data Sources'

TL;DR: Merge static and dynamic data in Power Apps into one flat table using the Ungroup function.

💡 Challenge

Combining multiple data sources, including hard-coded static data, into a single Power Apps structure adds real complexity to your app’s data management.

✅ Solution

Use the Ungroup function to flatten different data types into one unified table, making the data easier to interact with and use.

🔧 How It’s Done

1. Combine the sources into an array mixing static and dynamic data.

🔸 Build an array of objects containing local static entries and tables from your data sources.

🔸 Use a common property name (e.g. locItem) across all objects.

2. Flatten with Ungroup.

🔸 Specify the common nested property (locItem) as the one to ungroup.

🔸 The result is a single flat table containing all items.

Ungroup(
   [
      // Object 1
      {locItem:[{Date: Today(), ID:0}]},

      // Object 2
      {locItem: DelegationPlayground}
   ],
   // The 'locItem' property is the common property to ungroup
   locItem
)

🎉 Result

A streamlined, efficient data structure that’s easier to manage and query, boosting app performance and the user experience.

🌟 Key Advantages

🔸 Integrates disparate data sources into one structure.

🔸 Simplifies querying and data manipulation in the app.

🔸 Improves overall efficiency and maintainability.

🧩 Table() for Stacking, AddColumns + LookUp for Joining

Tested with t1 (ID 1 Alpha, ID 2 Beta), t2 (ID 3 Gamma) and cities (ID 1 Zurich, ID 2 Bern).

Stack sources with Table()

🔸 Table(t1, t2) returns three rows: Alpha, Beta, Gamma. Records and tables can be mixed in one call: Table({ID:0, Name:"Static"}, t1, t2) returns four rows, the static record first.

🔸 Stacking does not remove duplicates: CountRows(Table(t1, t1)) is 4. Use Distinct(Table(t1, t1), Name) to get ["Alpha", "Beta"].

🔸 Columns that only exist in one source are kept and left blank in the other rows: Table(t1, Table({ID:9, City:"Bern"})) returns the columns City, ID, Name, with City blank for Alpha and Beta and Name blank for ID 9.

Join on a key with AddColumns and LookUp

Stacking puts rows under each other. A join adds columns from the second source to each row of the first:

AddColumns(
    Table(t1, t2) As r,
    City,
    LookUp(cities, ID = r.ID, City)
)

🔸 Result: Alpha gets Zurich, Beta gets Bern, Gamma gets a blank City because cities has no ID 3 (a left join).

🔸 For an inner join wrap it in Filter(..., !IsBlank(City)): only Alpha and Beta remain.

🔸 Name the outer row with As r. Inside LookUp the unqualified ID refers to the cities row, so r.ID is needed to reach the outer row.

🎥 Video Tutorial

🛠️ FAQ

1. What is the main purpose of the Ungroup function in Power Apps? It flattens nested tables or collections into a single level by ungrouping a specific nested property, making mixed data sources easier to work with.

2. Can I use other functions to merge static and dynamic data? Yes, you can also use the Table function, but Ungroup gives more flexibility when combining different sources into one structure.

3. Is Ungroup delegable with large data sets? Ungroup itself isn’t delegable, but it works well on locally loaded collections or static data after the data has been retrieved from delegable sources.

4. How do I join two tables on a key in Power Apps? AddColumns(TableA As r, NewColumn, LookUp(TableB, Key = r.Key, Column)). Rows without a match get a blank NewColumn; filter them out with !IsBlank(NewColumn) if you want matches only.

5. What happens when the sources have different columns? Table() returns the union of all columns. A row keeps blanks in the columns its source did not have.

6. Does Table() remove duplicate rows? No. Table(t1, t1) returns every row twice. Use Distinct() on one column when you need unique values.

🔗 Related Tips

🔸 How to Load Multiple Data Sources into ONE Collection - a ClearCollect/AddColumns based approach to the same combining problem.

🔸 #PowerPlatformTip 71: ‘Array to Object’ - another data-reshaping trick, this time in Power Automate instead of Power Apps.