Collections: multiple columns of distinct

[fireflyxxoo]

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?

[Drrickryp]

@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]

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

[Drrickryp · Accepted solution]

@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.