Filter and Distinct

[neill_long]

Hi

I have a gallery which has the following formula:

Filter(
    LDR_UniversitiesTEST,Dropdown2_1.Selected.Value = Blank() And Department = CoursesList.Dept && Removed = "No" && IsBlank(SearchBox_2.Text) Or Session = Dropdown2_1.Selected.Value And Department = CoursesList.Dept && Removed = "No" && IsBlank(SearchBox_2.Text) || Department = CoursesList.Dept && Removed = "No" && StartsWith(
        Course,
        SearchBox_2.Text
    ) && Session = Dropdown2_1.Selected.Value And Department = CoursesList.Dept && Removed = "No" Or Department = CoursesList.Dept && Removed = "No" && StartsWith(
        Course,
        SearchBox_2.Text
    ) && Dropdown2_1.Selected.Value = Blank() And Department = CoursesList.Dept && Removed = "No")

This gallery show items where the department is equal to the selected record stored in a variable.  but I also need to do a distinct on it to remove the duplicate courses.

I added Distinct at the start and then at the end I added ,Course):

Distinct(Filter(
    LDR_UniversitiesTEST,Dropdown2_1.Selected.Value = Blank() And Department = CoursesList.Dept && Removed = "No" && IsBlank(SearchBox_2.Text) Or Session = Dropdown2_1.Selected.Value And Department = CoursesList.Dept && Removed = "No" && IsBlank(SearchBox_2.Text) || Department = CoursesList.Dept && Removed = "No" && StartsWith(
        Course,
        SearchBox_2.Text
    ) && Session = Dropdown2_1.Selected.Value And Department = CoursesList.Dept && Removed = "No" Or Department = CoursesList.Dept && Removed = "No" && StartsWith(
        Course,
        SearchBox_2.Text
    ) && Dropdown2_1.Selected.Value = Blank() And Department = CoursesList.Dept && Removed = "No"),Course)

But the fields in the gallery are now showing error.  e.g. label that had ThisItem.Course in its default.  It will only accept ThisItem.Result which shows the department name.

Is it possible for it to show the relevant fields instead of just the 'Result' of the distinct?

[neill_long]

If distinct is not the answer then maybe a GroupBy?  but can someone help?

[KeithAtherton · Accepted solution]

Hi @neill_long,

As you say, the Distinct function returns a one-column table of the results with duplicate values removed. The name of the column is Result.

The GroupBy function should help as you can return multiple columns using this format:

GroupBy( Table, ColumnName1 [, ColumnName2, ... ], GroupColumnName )

GroupBy function reference: https://learn.microsoft.com/en-us/power-platform/power-fx/reference/function-groupby

---

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

[cha_cha]

Hello @neill_long 

this is just how I usually do to when my code gets long but maybe someone can give you a better answer. 

(1) Duplicate your Gallery.

(2) Original Gallery will be Gallery A and Hide It.

(3) New Copied Galler will be Gallery B and you add the code below to its Item property.

With({_items: Distinct(GalleryA.AllItems,Course), _details:GalleryA.AllItems},
    ForAll(_items As A,
        Patch(LookUp(_details,Course=A.Result),{})
        ) 
    )

Gallery B would just be a duplicate of Gallery A.

[neill_long]

@cha_cha

When I enter your formula on the gallery, it should display 4 departments, but it only shows 2.  It will only show 4 when I scroll down on Gallery A to display records that have the remaining departments.  Is there something i need to add to ensure all 4 departments will show?

thanks

[cha_cha]

So does it mean some courses may have multiple departments?

[neill_long]

each course will have 1 department.  there are only 4 departments but there are multiple courses which will be connected to a department.

[cha_cha]

Sorry, can you please show me a sample result you want and what is currently showing? I'm bad at visualizing words.

[neill_long]

So this is what is currently showing: Gallery B:

On the 'Items' of Gallery B I have the following to show the Department Names:

With({_items: Distinct(Gallery3.AllItems,Department), _details:Gallery3.AllItems},
    ForAll(_items As A,
        Patch(LookUp(_details,Department=A.Result),{})
        ) 
    )

The hidden gallery, Gallery A, lists all the records, I have it showing the course names and the department:

This is what should be displaying:

[cha_cha]

Hi @neill_long 

With({_items: Distinct(GalleryA.AllItems,Course & "##" & Department), _details:GalleryA.AllItems},
    ForAll(_items As A,
        Patch(
            LookUp(_details
            ,Course=First(Split(A.Result,"##")).Result && Department=Last(Split(A.Result,"##")).Result
            ),{})
        ) 
    )