Blog
Data - How to hide duplicate rows in a gallery / show distinct multiple columns in a gallery
A common requirement to show multiple distinct columns, or to hide duplicate rows in a gallery control. This post highlights a technique to carry out this task.
Example of use case of showing multiple distinct columns
A typical example of where duplicates occur is when we join data from multiple tables.
Let's take the example of a set of tables in a helpdesk type application. There are two tables in this app - an issue table, and an 'issue response' table. The issue tables stores the main details of a support ticket. Each interation with a customer is stored as a seperate record in the 'issue response' table.
The schema of these tables are shown beneath.
The requirement is to build a search screen where users can display unique issue records that match the the issue response description.
The starting point is to create a SQL Server view that joins the issue and 'issue response' tables.
CREATE VIEW vwSearchIssue
AS
SELECT
iss.IssueID,
iss.Description AS [IssueDescription],
iss.TargetCloseDateTime,
isr.Description AS [IssueResponseDesc],
isr.CreateDateTime AS [IssueResponseCreateDate]
FROM
Issue iss JOIN
IssureResponse isr
ON iss.IssueID = isr.IssueID
From Power Apps, we can build a search feature by adding search text box, and a gallery control with the Items property set to the following formula:
Search(vwSearchIssue, txtSearchInput.Text, "IssueResponseDesc")
The problem here is that if multiple matches exist in the 'issue response' table, the result will include duplicate issue details.
How to remove duplicates from a gallery control
To illustrate the problem, here's the data that the view would return if the user were to search for the word 'user' in the response description. Notice the duplicates that are highlighted in red.
To display these distinct values in a gallery control, we would call the GroupBy function to group the result by IssueID. We would set the Items property of our gallery control to this same formula.
GroupBy(Search(vwSearchIssue, txtSearchInput.Text, "IssueResponseDesc"),
"IssueID",
"Data"
)
This effectively produces a result that looks like this.
Each record in this result includes an IssueID field and a child table called 'Data' that contains all the rows that
match the corresponding issue ID.
From the item template of the gallery, we can show the distinct 'Issue Description', and 'Target Close Datetime' fields by looking up the first row in the data child table. The syntax to return the IssueDescription would look like this:
First(ThisItem.Data).IssueDescription
First(ThisItem.Data).TargetCloseDateTime
Conclusion
A common requirement is to hide
duplicate rows in a gallery control, or to show distinct values accross multiple columns. This post
demonstrated how to carry out this task by grouping the results with the.GroupBy function.
- Categories:
- data
- Data - How to find the common rows from 3 or more collections
- Data - How to show the distinct rows from 2 data sources or collections
- 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 - 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