Filter with "Distinct"

[Shannon_Marceau]

Could somebody show me the syntax for a typical gallery filter

SortByColumns (Filter.....

That not only grabs the appropriate records from a Sharepoint but also further filers to show unique items only.

I figured out hoe to use the Distinct Function, but not how to incorporate it into the Syntax generated for the gallery.

[Anonymous]

Could you, perhaps, provide a bit more detail on what you are trying to accomplish?  The Distinct function returns distinct values from one column in a data set.  For example, given the Data set "people":

fname, lname, birthmonth

John, Smith, February

Jane, Smith, June

Rick, Runner, February

Paul, Jones, September

Distinct(people, birthmonth)  would give me back a list with only "February, June, September."  That's it.

In a Gallery, you are typically showing multiple fields from all the (top n) rows in your data set, so filtering on "Distinct" doesn't seem to make sense...  So, would you mind describing your goal in more detail?

[Shannon_Marceau]

Sorry....it was clear in my head. I have a typical gallery that that was autogenerated froom a sharepoint site called training matrix and includes columes labeled  Title, First Name, Last name etc. The gallery shows all the records like it should

SortByColumns(Filter("Training Matrix", StartsWith (Tiyle, TextsearchBox1.text)), "Title", If(SortDescending1, Descending , Ascending))

This populates the gallery as expected, and i would like to further  filter the results based on the Title column to show only unique vales in the Title columns. I can get that in a dropdown with Distinct(Training Matrix, Title) But I would like this to happen based on the input in TextSearchBox.1.......this will eliminate double records.

[Anonymous]

Let me see if I understand correctly.  You have a dataset something like this:

Safe Toaster Usage, John, Smith, etc.

Safe Toaster Usage, Jane, Smith, etc.

Microwaves 101, John, Smith, etc.

Microwaves 101, Fred, Jones, etc

Microwaves 201, Betty, Jones, etc.

Microwaves 201, John, Smith, etc.

And given the text in TextsearchBox1 of "micro", you want the gallery to whittle down to just

Microwaves 101 and Microwaves 201.

Yes?

But the problem is those other pesky values in the rows, right?  If you only want one instance of Microwaves 101, which one should be kept?  The John Smith row or the Fred Jones row?  That's where Distinct is failing you.  So we need to figure out the end goal here...

So, let's keep the conversation going.  Why do you want Distinct Titles?  Where are you going with that next?

[Shannon_Marceau]

I appreciate your time on this,

My ultimate goal is to Use the gallery to show all the Employee Names from a sharepoint site and use the searchbox to  display one name only. I use that selection to populate an invisible textbox that passes the name via Navigate to another Gallery. Then I show ALL the training records for that employee in another gallery.

[Anonymous]

Okay.  So, again, just to make sure I'm understanding, we're not really looking to find Distinct "Title" values in step one; we're looking to find a distinct Employee Name in step one.  Then step two would be to show all records from the training matrix for that employee.

And our source for Employee Names is the Training Matrix?  And if so, do I understand correctly that the Training Matrix has first and last name as distinct values?

[Shannon_Marceau]

I think I may have confused things.

Training Matrix is the name of the Sharepoint list. It has columns ID-Title -First Name- Last Name- and Type of Training

The "Title" column is actually called  "Combined" in Sharepoint and combines the first and last name. For some reason Powerapps refers to the original name "Title" and not the name I changed it to. My Gallery returns multiple records with "Title" (the concatenated name of the person" and the " Type of training".......

I woud like to have the gallery only display the "Title" Column (emplyee names)  with no repitition. The user can then search John Doe and reduce the gallery to one employee name that i can use to populate another gallery and show alll the training records for that employee.

[Anonymous]

Got it.  (This is where tiny little dataset examples help illustrate very well.)

Okay, so given this dataset:

John Smith, John, Smith, Microwaves 101

John Smith, John, Smith, Microwaves 102

John Smith, John, Smith, Toaster Safety

Bob Jones, Bob, Jones, Microwaves 101

Bob Jones, Bob, Jones, Safe Lightswitch Operation

Carly Simon, Carly, Simon, Mixer Board 201

You want them to type in "bob" and have the list (perhaps a gallery, perhaps another control) show only:

"Bob Jones"

So, based on the code snippet you sent earlier, try this:

SortByColumns(Filter(Distinct("Training Matrix", "Title"), StartsWith ("Title", TextsearchBox1.text)), "Title", If(SortDescending1, Descending , Ascending))

Distinct returns a single-column table, and that column should keep its name, "Title", so that should pass through to your StartsWith and your SortByColumns.  Let me know...

The next question will be, "How do they search by last name?"  And that's a different conversation...  🙂

[Shannon_Marceau]

I have tried this statement and I receive an error message. The third "Title" has a red squiggle under it and the message says Invalid name. I also noted that when I type the first occurance for "title (in the distinct function) i see the auto populate under the formula bar with the word "Title" and I can click on that to enter the rest of the word.......the second time i type "Title in the formula it doesnt auto generate and I have to type the whole word, but it accepts it without gererating an error. The third Iteration before the sort ascending doesnt show the autopopulate and throws a red line under the word.

[Hockeyman9474]

If I may piggyback with a similar concern. I am developing my first powerapp. My datasource is  an excel spreadsheet. One of my fields is location. I want to view locations that are 6 in length and all numeric such as 020148. There could be several records for each one. Records in this column that do not match that criteria are have hyphens, spaces, and alpha characters. I have mastered Distinct and mastered filtered, but I have not been able to combine them. I have something like this:

Distinct(Filter(Mid(Location, 4,1)<>"-"))

Ideally I'd like to say where length=6 or where Location,7,1 is not numeric but I don't think that's possible.

I will add that I am using a Listbox. In my research, I have found most prefer galleries, but I am new to powerapps and don't understand the concept or the explanations as most explanations are not clear.

One last question while I am at it, can you suggest any youtube channel where this is all explained clearly. Most videos show app creation from a sharepoint site doc and don't include any coding/functions etc.