Power Apps: Merge tables/collections and get distinct records (without using Collect)

Imagine a case where you have two tables/collections in a Power Apps canvas app, and you want to merge those tables in a single one to be used somewhere else. The common practice is to use collections and to collect all data from those tables in a new collection.

The problem of this approach

If this collection is used in a single place, with no data editing need, an additional third collection would be created only for this purpose. And also if you wanted to do duplicate detection, you would have to add additional logic to your collect formulas.

The alternative

In this example, two tables are stored in variables (or it could be also in collections) with two fields only, Email and DisplayName. And the need is to have a third table generated by merging the original two tables, plus obtaining only the distinct records (see the names Thaddeus and Daniel are in both tables, and duplicates were removed in the third one):

Sample data tables (all names are fictitious)

To help us with this approach, we can use the With function, in combination with the GroupBy, Ungroup and ShowColumns functions. In summary, what the final formula will do:

  • Create a temporary Table where each item have a child table (that will be the tables 1 and 2)
  • Ungroup the table above, so all child items are in the same table
  • Group the table by the fields we want to keep distinct and use the ShowColumns formula to explicitly tell what columns have to be returned and then remove the unnecessary grouped data column

Here is the formula to do this trick:

With(
    {
        outputTable: Ungroup(
            Table(/* Create the table with child tables. If you want to join more tables, add more items to this table and the formula will work the same way*/
                {tableObject: Table1},
                {tableObject: Table2}
            ),
            "tableObject"
        )/* Merges all the tables into a single one, but no duplicates removed */
    },
    ShowColumns( //Display only the columns needed
        GroupBy(
            outputTable,
            "Email",//add the columns to extract distinct data. any column to be used has to be added here, and in the ShowColumns piece
            "DisplayName",
            "Grouped"//Last parameter can be any name as it's just the name for a child table with grouped data
        ),
//same columns used on the GroupBy piece except the last one (the child table name)
        "Email",
        "DisplayName"
    )
)

You can use this formula as the Items property for data tables, galleries or other controls, instead of collecting the data to a new collection and using it as the items source.

Conclusion

By using a single formula we can eliminate the need to create additional collections when we need to merge tables/collections and keep the app code cleaner. The same formula can be used with data coming directly from data sources (instead of the local data Table1/Table2 as mentioned), but be aware of possible delegation issues.

5 comments

Leave a Reply

Your email address will not be published. Required fields are marked *