Blog
Data - How to show the distinct rows from 2 data sources or collections
February 26. 2024
If you want to combine the contents of 2 collections or data sources and return the distinct rows, this post walks through an example of how to carry out this task.
A pretty common task is to show the distinct rows from 2 or more collections.
This post walks through the formula to carry out this task.
Walkthrough - Showing the distinct rows from 2 or more collections
As an example, let's take the following 2 collections - CollectionA and CollectionB.
ClearCollect(CollectionA,
{FirstName: "Alice", Surname: "Johnson", Email: "alice.johnson@test.com"},
{FirstName: "Bob", Surname: "Smith", Email: "bob.smith@test.com"}
);
ClearCollect(CollectionB,
{FirstName: "Bob", Surname: "Smith", Email: "bob.smith@test.com"},
{FirstName: "Charlie", Surname: "Brown", Email: "charlie.brown@test.com"}
);
With these 2 collections, Bob Smith appears in Collection A and Collection B. Therefore, the end result is to produce a collection that contains 3 records which correspond to Alice, Bob, and Charlie.
GroupBy(The screenshot beneath shows the result when we set the Items property of a data table to this formula.
Ungroup(Table(
{collection: CollectionA},
{collection: CollectionB}
),
"collection"
),"FirstName", "Surname", "Email","DistinctGroup"
)
The core part of this formula calls the Table function to create a table with 2 rows - each row contains a single column called "collection" that contains the content of a collection.
The call to the Ungroup function ungroups this table by the "collection" column. The Ungroup function creates a single table that combines the content of CollectionA and CollectionB.
This ungrouped table duplicates the "Bob" record that appears in CollectionA and CollectionB. Thefore, to create an output that removes this duplication, we call the GroupBy function and specify the unique columns to show for each row - in this case, FirstName, Surname, and Email.
The GroupBy function requires us to specify a column to store any remaining columns. Here we specify the column name DistinctGroup.We can call the DropColumns function to remove this column if needed.
To create a collection to store this result, we can use the formula shown beneath.
.
ClearCollect(FinalCollection,
DropColumns(
GroupBy(
Ungroup(Table(
{collection: CollectionA},
{collection: CollectionB}
),
"collection"
),"Name","Email","DistinctGroup"),
"DistinctGroup")
);
Conclusion
A common task is to show the distinct rows from multiple collections and this post walked through an example of how to do this.
- Categories:
- data
Related posts
- Data - How to implement circular rotational date sorting
- Bug - What to do when the data section of the Power Apps Maker portal doesn't work
- Data - Combine columns from separate tables into a single table
- Formula - Transposing/converting rows to columns- an almost impossible task?
- Data - How to rename field names in a record
- Data - How to hide duplicate rows in a gallery / show distinct multiple columns in a gallery
- Data - Retrieving news/forum/blog articles with RSS
- Data - How to sort by partial numbers in a text field
- Data - How to return the last record from a table
- Data - How to create bulk test/dummy records with random values
- Data - 3 things you should know before using the MySQL or PostgreSQL connectors
- Data - A walkthrough of how to migrate the data source of an app from Excel to Sharepoint
- Data - How to enforce unique values (or prevent duplicate values) in one or more columns
- Data - How much mobile data does Power Apps consume? What ways can we minimise this?
- Data - How to save and retrieve Google calendar entries
- Data - How to save and retrieve Google contacts
- SQL - Caution! This is how users can hack shared SQL connections
- SharePoint – 2 Mistakes to avoid when importing Excel data
- SQL - Don't let this DateTime bug catch you out!
- Settings - What's the purpose of the "Explicit Column Selection" Setting?
- SQL Server for Beginners Part 3 - Installing On-Premises Gateway
- SQL Server for Beginners Part 2 - Installing Management Studio
- SQL Server for Beginners Part 1 - Installing SQL Server
- Searching data–What you need to know about case sensitivity
- Images - How to create images that can change depending on data
- Excel - Reasons NOT to use Excel as a data source
- SharePoint - What you need to know about Filtering Data
- Formulas - Generating Row Numbers