cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Whitemast
Level: Powered On

Filter a non-distinct list into a distinct list

Hi 

 

I am currently having some trouble trying to filter a sharepoint list into distinct list based on a latest dates for each entry with the help of a searc bar. 

 

The sample table that I currently have formated up to current looks like this: 

 

Carpark lot number Last updated current status 
A11/26/2018Used
A11/20/2018Empty
A11/15/2018Used
B11/24/2018Empty
B11/15/2018Used
C11/16/2018Empty
C11/15/2018Used
D11/15/2018Empty

 

My current code for items to achieve this format is as follows :

SortByColumns(SortByColumns(Search('sample table',SearchText1.Text,"Carpark lot number"),"Last Updated",Descending),"Carpark lot number",If(SortDescending1, Descending,Ascending))

 

However, what I am looking to do is as follows: 

 

Carpark lot number Last updated current status 
A11/26/2018Used
B11/24/2018Empty
C11/16/2018Empty
D11/15/2018Empty

 

Is there a way to get to the desire format? I have tried the distinct function but it only gives me a single entry from the entire table due to each entry having a different latest date. 

 

The code that I tried looks like this: 

 

Filter('sample table', Last updated =First(Sort(Distinct('sample table',Last updated),Result,Descending)).Result)

 

Hope that I can get some advice on how to modify my codes. Thanks for the help.

 

1 ACCEPTED SOLUTION

Accepted Solutions
TimW
Level: Powered On

Re: Filter a non-distinct list into a distinct list

OK, change of approach - I tested this although with a different table and field names:

AddColumns(GroupBy('sample table', "Carpark lot number", "Details"), "Latest", First(Sort(Details.'Last updated', Descending)).'Last updated')

Breaking it down, GroupBy creates a new table with a column for the key (Carpark lot number) and a column containing a sub table for the details (in this case the column is also called Details). AddColumns adds a new column to this table called Latest which is calculated by sorting the Details sub table by Last updated and then getting the first result.

 

Hope that makes sense!

View solution in original post

8 REPLIES 8
TimW
Level: Powered On

Re: Filter a non-distinct list into a distinct list

I'll have a go - using an intermediate collection (I haven't tested it):

ClearCollect(colSortedSampleTable, Sort('sample table', 'Last updated', Descending));
ClearCollect(colLatestItemSampleTable, Filter('sample table', LookUp(colSortedSampleTable, 'Carpark lot number' = 'sample table'[@'Carpark lot number'])))

or if it has to go into a non action formula then:

Filter('sample table', LookUp(Sort('sample table', 'Last updated', Descending), 'Carpark lot number' = 'sample table'[@'Carpark lot number']))

but this doesn't look very efficient - the sort runs once for each record!

Whitemast
Level: Powered On

Re: Filter a non-distinct list into a distinct list

Hi TimW, 

 

Thanks for the advice, however I seem to have an error with both methods. 

 

For the clearcollect method, the item property of the browse gallery list does not accept the clearcollect function at all. Am I suppose to put it there in the first place? 

 

As for the filter method, I seem to have an error with lookup function especially with the condition section. It seems to be expecting a different argument type, which in turn causes the filter condition to fail. 

 

Is there any way to fix the errors? 

TimW
Level: Powered On

Re: Filter a non-distinct list into a distinct list

OK, change of approach - I tested this although with a different table and field names:

AddColumns(GroupBy('sample table', "Carpark lot number", "Details"), "Latest", First(Sort(Details.'Last updated', Descending)).'Last updated')

Breaking it down, GroupBy creates a new table with a column for the key (Carpark lot number) and a column containing a sub table for the details (in this case the column is also called Details). AddColumns adds a new column to this table called Latest which is calculated by sorting the Details sub table by Last updated and then getting the first result.

 

Hope that makes sense!

View solution in original post

Whitemast
Level: Powered On

Re: Filter a non-distinct list into a distinct list

Hi TimW, 

 

Thanks for the advice, it works!

 

However, how to I link it back to the old table? Cause I have a link to for the entries to be edited. 

 

 

TimW
Level: Powered On

Re: Filter a non-distinct list into a distinct list

You should be able to use the Ungroup or LookUp function to do that - how is it linked?

Whitemast
Level: Powered On

Re: Filter a non-distinct list into a distinct list

Hi TimW, 

 

Thanks for the reply, the list is currently the gallery page of the standard 3 page setup given by the power apps.

 

After I have managed to filter the list into the new table, the orginal links to the details page and edit page will no longer work. (The data from the pervious list will also no longer be displayed on the gallery list as well). 

TimW
Level: Powered On

Re: Filter a non-distinct list into a distinct list

You have a table like this:

Carpark lot number Last updated current status 
A11/26/2018Used
A11/20/2018Empty
A11/15/2018Used
B11/24/2018Empty
B11/15/2018Used
C11/16/2018Empty
C11/15/2018Used
D11/15/2018Empty

 

which has been summarised to this:

Carpark lot number Last updated 
A11/26/2018
B11/24/2018
C11/16/2018
D11/15/2018

 

What should happen when the user clicks on the summarised row? For example, if they click on Carpark lot number A then there are 3 related records:

 

Carpark lot number Last updated current status 
A11/26/2018Used
A11/20/2018Empty
A11/15/2018Used
Administrator
Administrator

Re: Filter a non-distinct list into a distinct list

Thanks for your help with this post @TimW!

 

@TopShelf-MSFT

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 137 members 4,978 guests
Please welcome our newest community members: