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

Display only last revision of filtered data

Dear all,

I am trying for several days about this matter.

I have an excel file as data source containing a table with various columns: Type of document, name of document, revision n°, date of revision.

The rows are filled of varius manuals and some manuals have same type, same name, but different revision n° and date.

What I want to do is that selecting via a dropdown control, the user select the type of manual and into a datatable or Browsegallery will appears a list of all manuals related to that manual type but only the latest revision.

For example into the table I have 2 rows with same document type called "checklist" and document name "checklist A", one with revision 1 and one with revision 2 and their date. Then other 3 rows with same document type "checklist" and document name "checklist B"respectively with revision 1, 2 and 3.

I wish that the user, by selecting via dropdown control the "checklist" option, can see inside the datatable below 2 rows: Checklist A with revision 2 and its related date, and the second row the checklist B with revision 3 and its date, not all revisions.

I tried by using:

Filter(Datab;TipoManuale=Dropdown8.SelectedText.Value) as first selection but I am not able to make it select the latest revision of each manual.

Any suggestion?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Display only last revision of filtered data

Hi @Maurom82 

I would load your Excel data into a local collection in the OnVisible property of your screen, using syntax like this:

 

ClearCollect(Documents; 
             AddColumns(GroupBy(DocumentList; "DocName"; "Result"); 
                        "RevNo"; 
                        First(SortByColumns(Result;"RevNo";Descending)).RevNo);
                        "DocDate"; 
                        First(SortByColumns(Result;"RevNo";Descending)).DocDate);
                        "DocType"; 
                        First(SortByColumns(Result;"RevNo";Descending)).DocType)
)

For the Items property of your data table, you can then filter the Documents collection by the DocType column.

---------------

To explain this a little further, the GroupBy function groups a table by the column that you specify. Let's say you start with data that looks like this.

image.png

 

Here's the result when we apply the following formula.

GroupBy(DocumentList; "DocName"; "Result")

image.png

There are 2 columns - the document name column and a column called 'Result' that contains a child table of the remaining rows.

In the initial formula in this post, we call the AddColumns function to add the other fields to the top level of the table. The formula sorts the 'Result' child table by revision number descending, and calls the First function to retrieve the first result. The result of this visually looks like this:

image.png

Hope that's of some help to you.

View solution in original post

5 REPLIES 5
datamaster
Level 8

Re: Display only last revision of filtered data

Hi @Maurom82  Try to do a GroupBy column for the name of document to load the drop down with only 1 unique document name in the drop down. Then when the user selects the document in the drop down you can do a lookup for the latest revision buy first sorting the data source in descending order (Latest document) as the lookup will return the first match it sees. 

 

 

------------------------------------------------------------------------------OfficePowerUser.com------------------------------------------------------------------------ 
If this post helps answer your question, please click on “Accept as Solution” to help other members. If you thought this post was helpful, please give it a Thumbs Up. 

Super User
Super User

Re: Display only last revision of filtered data

Hi @Maurom82 

I would load your Excel data into a local collection in the OnVisible property of your screen, using syntax like this:

 

ClearCollect(Documents; 
             AddColumns(GroupBy(DocumentList; "DocName"; "Result"); 
                        "RevNo"; 
                        First(SortByColumns(Result;"RevNo";Descending)).RevNo);
                        "DocDate"; 
                        First(SortByColumns(Result;"RevNo";Descending)).DocDate);
                        "DocType"; 
                        First(SortByColumns(Result;"RevNo";Descending)).DocType)
)

For the Items property of your data table, you can then filter the Documents collection by the DocType column.

---------------

To explain this a little further, the GroupBy function groups a table by the column that you specify. Let's say you start with data that looks like this.

image.png

 

Here's the result when we apply the following formula.

GroupBy(DocumentList; "DocName"; "Result")

image.png

There are 2 columns - the document name column and a column called 'Result' that contains a child table of the remaining rows.

In the initial formula in this post, we call the AddColumns function to add the other fields to the top level of the table. The formula sorts the 'Result' child table by revision number descending, and calls the First function to retrieve the first result. The result of this visually looks like this:

image.png

Hope that's of some help to you.

View solution in original post

Maurom82
Level: Powered On

Re: Display only last revision of filtered data

Dear @timl ,

thanks a lot for your answer, it seems that your answer will meet my needs.

Sorry but I don't understand some steps you told, I am not able to replicate the code below:

"

ClearCollect(Documents; 
             AddColumns(GroupBy(DocumentList; "DocName"; "Result"); 
                        "RevNo"; 
                        First(SortByColumns(Result;"RevNo";Descending)).RevNo);
                        "DocDate"; 
                        First(SortByColumns(Result;"RevNo";Descending)).DocDate);
                        "DocType"; 
                        First(SortByColumns(Result;"RevNo";Descending)).DocType)
)

I think that "Documents" is the name of the new table that will be created on the "OnVisible" property, isn't it? So the "DocumentList" is the name of excel table where are listed documents.

I don't understand the Groupby (DocumentList; "DocName"; "Result") because "Result" is not a column.

---------------------

I explain with data,this below is the table taken from excel file, the table is named "Datab":1.jpg

 

 

 

I tried to reply your code into the OnVisible property like this:

 

ClearCollect(Documents; 
             AddColumns(GroupBy(Datab; "Manuale"; "Result");
                        "NumRev"; 
                        First(SortByColumns(Result;"NumRev";Descending)).NumRev);
                        "DataRev"; 
                        First(SortByColumns(Result;"NumRev";Descending)).DataRev);
                        "TipoManuale";
                        First(SortByColumns(Result;"NumRev";Descending)).TipoManuale)
)

but I can see no collection on the collection folder.

What is wrong?

 

Super User
Super User

Re: Display only last revision of filtered data

Hi @Maurom82 

To confirm your 2 questions, the answer to both is yes. "Documents" is the name of the new collection that will be created through the ClearCollect function. "DocumentList" is the name of excel table where your documents are listed.

With regards to call to the GroupBy function, Result is the name of the column that the GroupBy function will create. Therefore, it's ok that there isn't a Result column in your source Excel data.

image.png

I'm not sure why the collection doesn't appear for you. To diagnose this, I'd try adding the formula to the OnSelect property of a button. When you click the button, does the collection appear for you?

 

 

 

 

Maurom82
Level: Powered On

Re: Display only last revision of filtered data

Thanks a lot timl,

sorry for delay but your suggestion works great.

 

Thanks again.

 

Mauro

Helpful resources

Announcements
thirdimage

Power Apps 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

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Kudoed Authors (Last 30 Days)
Users online (4,146)