How to get distinct Gallery tiles in PowerApps?

Question

My DataSource for PowerApps is Excel Table. After user's Entry, the excel Table looks like below. In below image one can see that "Sys1" appears twice in Excel Table, leading to creating one extra tile for same system Gallery in PowerApps.My Question is How to avoid duplication of tile creation in PowerApps ? Below is code for Gallery and Tile. I am new to PowerApps. Provide code with explanation .

Note: I forgot to add Name after Sys. The 1st column name in below screenshot should be "Sys Name"

Gallery ->Items Property [CODE]

Filter(Table1,Startswith('Sys Name'),"Sys"))

Tile -> Text Property [CODE]

ThisItem.'Sys Name'

carlosfigueira · Answer 1 · 2020-10-29T15:08:05.333

2

To remove duplicates you can use the GroupBy function and take the First of the elements in each group. Once you have that, you can use some of the table shaping functions (AddColumns, DropColumns) to recreate the original column structure, if necessary:

DropColumns(
    AddColumns(
        GroupBy(
            Filter(Table1, StartsWith('Sys',"Sys")),
            "Sys",
            "BySys"),
        "Model#", First(BySys).'Model#',
        "Current Status", First(BySys).'Current Status',
        "Previous Status", First(BySys).'Previous Status'),
    "BySys")

The way you can read the expression above is from inside out: first filter the Table1 only for those rows whose 'Sys' column starts with "Sys" (what you had originally). The result of the filter will be grouped by the 'Sys' column, with all rows that have similar values grouped in the 'BySys' column. To this result, we add three columns: 'Model#', 'Current Status' and 'Previous Status', by taking the first of the grouped elements. Finally we remove the grouped column ('BySys') at the outermost function.

If you don't want to have to list all of the properties of the original data source in the expression, you can stay with the GroupBy expression as the Items of your gallery:

GroupBy(
    Filter(Table1, StartsWith('Sys',"Sys")),
    "Sys",
    "BySys")

In the gallery template, you can have a label that shows the 'Sys' column directly as ThisItem.Sys, but if you want to access the other columns, you will need to choose, from the group, what you want to display. For example, to display the model number of the first row for that specific 'Sys' value, you can have this expression as the Text property of a label:

First(ThisItem.BySys).'Model#'

Yet another option, if you want to show many other properties and don't want to keep repeating the call to First is to add that as another (record) property of the gallery items:

AddColumns(
    GroupBy(
        Filter(Table1, StartsWith('Sys',"Sys")),
        "Sys",
        "BySys"),
    "FirstSys", First(BySys))

And now in your gallery you can have labels with the following properties:

ThisItem.FirstSys.'Model#'
ThisItem.FirstSys.'Current Status'

And so on.

edited Oct 29 '20 at 15:08

answered Oct 28 '20 at 20:39

carlosfigueira

85,035
14
131
171

Wow. That was a lot harder than I thought it would be! – SeaDude Oct 29 '20 at 04:13
@carlosfigueria...Thanks! Will DropColumns drop any columns from Excel Table ? Do I need mention all column names that are present in Excel Table ? (Ex: "Model#","Current Status,Previous Status) – biggboss2019 Oct 29 '20 at 11:41
Getting an Error: The Function 'AddColumns' and 'DropColumns' has some invalid arguments. Name isn't valid. This identifier isn't recognized. – biggboss2019 Oct 29 '20 at 13:37
I am adding above code in Items property of Gallery – biggboss2019 Oct 29 '20 at 13:43
To your first question: DropColumns / AddColumns will not change the original data source (the Excel table), it will create a new table (in memory) with the changes applied... Regarding mentioning all column names - not really - you can have the GroupBy expression in the Items property of the gallery, and if you can access the fields from the group (BySys). I'll update the answer with this other option. – carlosfigueira Oct 29 '20 at 15:03
For your error: try setting the Items properties in the gallery from the inner expression to the outer most, to identify where the error is coming from. For example, if you only have the Filter expression, does it work? Great. Then if you have the GroupBy(Filter...), will it work? And so on, until you can find the place where the error originates. – carlosfigueira Oct 29 '20 at 15:09
@carlosfigueira..Looks like my Filter expression is fine. However, when I run GroupBy expression I getting an error. I have space Sys and Name (ex: Sys Name). For this reason I cannot see my column Sys Name. PowerApps is showing me "Sys_x0200_Name". Note: In above screenshot I forgot to add Name after "Sys" column. I updated my question now. – biggboss2019 Nov 02 '20 at 12:19
Not sure how to fix the issue. Any suggestion ? Thanks in advance! – biggboss2019 Nov 02 '20 at 12:22
If the column name is 'Sys Name', then the GroupBy expression should be either `GroupBy(Filter(Table1,StartsWith('Sys Name',"Sys")),"Sys Name","BySys")` or `GroupBy(Filter(Table1,StartsWith('Sys Name',"Sys")),"Sys_x0200_Name","BySys")`... As you type the `,` after closing the second parenthesis, the editor should show you a list of columns that you can use. – carlosfigueira Nov 02 '20 at 16:52
@carlosfigueira..Please see my issue mention here ->https://stackoverflow.com/questions/64652910/groupby-function-nor-displaying-gallry-tiles-in-powerapps – biggboss2019 Nov 02 '20 at 20:34

score 1 · Answer 2 · answered May 21 '21 at 16:11

Another way is to use the "Distinct function" like below

Distinct(Table1, Column_Name)

But the issue with this is, it gives out a table with only one column called "Result" which you can rename with the code below.

RenameColumns(Distinct(Table1, Column_Name), "Result", "Renamed_Column")

How to get distinct Gallery tiles in PowerApps?

2 Answers2

Linked