cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fireflyxxoo
Helper I
Helper I

Collections: multiple columns of distinct

Hello.

 

I have a data that contains State, City, and Status

 

I am trying to extract distinct values from these columns and put them in one collection so I can use them in combo box later. I want to maintain the columns as they are. I need distinct values for filtering later on.

 

I created a collection but the values weren't distinct and also when I used distinct formula it renames the column names so I wasn't able to merge them together later.

 

 

How should I go about it?

1 ACCEPTED SOLUTION

Accepted Solutions
Drrickryp
Super User
Super User

@fireflyxxoo 

Assuming a combobox, cb1 with items property as in the first formula and cb2 would be 

 

Sort(
    RenameColumns(
        Distinct(
            Filter(
                yourdata,
                State in cb1.SelectedItems
            ),
            City
        ),
        "Result",
        "City"
    ),
    City,
    Ascending
)

You will receive delegation warnings as the Distinct() function and the "in" operator are not delegatable in SharePoint. As long as your data is not more than the record limits (500 default, 2k maximum), it can be ignored.

 

 

View solution in original post

3 REPLIES 3
Drrickryp
Super User
Super User

@fireflyxxoo 

Distinct() takes a single column from a list, removes all the duplicate values and produces a single column table with the column name Result.  If you want a collection of unique values for States to use in a dropdown control (say dd1) from your data and you want to have the column named States, sorted alphabetically: 

 

ClearCollect(
             colStates,Sort(
                           RenameColumns(
                                    Distinct(
                                             yourdata,State
                                    ),"Result","State"
                              ),State, Ascending
                         )
)

To obtain a table of cities in the state selected in the dropdown control above, you would use the following formula for the Items property of another dropdown control.

Sort(
    RenameColumns(
        Distinct(
            Filter(
                yourdata,
                State = dd1.Selected.State
            ),
            City
        ),
        "Result",
        "City"
    ),
    City,
    Ascending
)

 

 

fireflyxxoo
Helper I
Helper I

I need to use a combo box not a drop down because I want multiple selection 

Drrickryp
Super User
Super User

@fireflyxxoo 

Assuming a combobox, cb1 with items property as in the first formula and cb2 would be 

 

Sort(
    RenameColumns(
        Distinct(
            Filter(
                yourdata,
                State in cb1.SelectedItems
            ),
            City
        ),
        "Result",
        "City"
    ),
    City,
    Ascending
)

You will receive delegation warnings as the Distinct() function and the "in" operator are not delegatable in SharePoint. As long as your data is not more than the record limits (500 default, 2k maximum), it can be ignored.

 

 

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (1,634)