Data - How to hide duplicate rows in a gallery / show distinct multiple columns in a gallery

July 09, 2021

3 min read

Data

There are many use-case scenarios where it's necessary to show sets of distinct values, or to hide duplicate rows. The challenge that app builders face is that the Distinct function is capable of returning distinct values from a single column only.

https://docs.microsoft.com/en-gb/powerapps/maker/canvas-apps/functions/function-distinct

Therefore, this post describes a technique to hide duplicates that doesn't rely on the Distinct function.

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

Likewise, this would return the 'Target Close Datetime' field.

First(ThisItem.Data).TargetCloseDateTime

As the screenshot beneath shows, we can use this technique to configure our gallery control to show just the distinct IssueID, 'Issue Description', and 'Target Close Datetime' fields that relate to the search.

Note that a caveat of this method is that the GroupBy function is not delegable. In most search scenarios however, this behaviour is generally acceptable because users typically enter criteria that filters matching records to a value under 2000 rows.

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.