Displaying distinct items sorted by soonest month

[EpicTriffid]

Afternoon all,

I'm looking to display two columns ("Month", "FAQ Item Name") from a SharePoint list that contains information about when certain reports are updated for an information app i'm creating.

I need to see a distinct list of "FAQ Item Name" (Which has multiple iterations of items each with different dates) where it shows the item with the soonest occurring month. This is so I can create a table that shows, for example, the next three reports that will be updated. I pull this into a "calPre" collection.

There is a slight problem in that "FAQ Item Name" is a lookup column, so it comes into a collection as a record that a table can't parse the text from, as well as having a number of blank records in that column that i need to filter out.

My solution to this is to pull that first collection (calPre) into a second collection (calPre2) that removes my blank rows and just gives me the two columns i'm interested it:

Clear(calPre2);
ForAll(
    Filter(
        calPre,
        Not(IsBlank(FAQ_x0020_Item_x0020_Name))
    ),
    Collect(
        calPre2,
        {
            FAQItem: Text('FAQ Item Name'.Value),
            dateMonth: Month
        }
    )
);

So now I have my two columns. You can see an instance above of the information that I might want to sort out: I would need to have a distinct list of the FAQItem column where the items that are returned are the ones with the soonest occurring date. My thinking is that I would create a new collection every time the app opens that would recalculate this, and I could sort it by the dateMonth column, then show the TopN number of results to get my 3 reports that will be updated soon. 

I've tried GroupBy, AddColumns, filtering by date, all the variations of Text() or Distinct().value/result and I either don't get anything returned, the dates are wrong, or it brings back lines and lines of [Object object] errors. 

I'm having real difficulty with something that I thought would be quite simple. Any help would be greatly appreciated!

[RandyHayes]

@EpicTriffid 

In general, the formula you want is the following:

Filter(
    GroupBy(
        SortByColumns(
            AddColumns(yourDataSource,
                "_FAQname", 'FAQ Item Name'.Value
            ),
            Month, Descending
        ),
        "_FAQname",
        "FAQs"
    ),
    !IsBlank(_FAQname)
)

This will provide you a table with distinct FAQ names (not blank) with a column called _FAQname that will have the FAQ name from the Lookup and a column called FAQs with a table of all the associated FAQs where the first record will be the most recent.

I hope this is helpful for you.

[EpicTriffid]

Hi @RandyHayes 

So close! I plugged this into a ClearCollect called calendarCol and got out my distinct list, but returned a column with [object Object] in it? 

It could be that I misunderstand and the idea is to never show that column that would have [object Object] in it, but it would be helpful if I could view the date as well (which i assume is lurking inside that FAQs column above?)

Thank you by the way. I really appreciate the help.

[RandyHayes · Accepted solution]

@EpicTriffid 

I would not suggest a table control for this as you will not have flexibility to control what is shown in the FAQs column.

If you are going to do it in a Table, then you will need to shape your data for that table. Set this as the Items property of the Table:

AddColumns(
    Filter(
        GroupBy(
            SortByColumns(
                AddColumns(yourDataSource,
                    "_FAQname", 'FAQ Item Name'.Value
                ),
                Month, Descending
            ),
            "_FAQname",
            "FAQs"
        ),
        !IsBlank(_FAQname)
    ),
    "MostRecent", First(FAQs).Month
)

Then show the _FAQname and the MostRecent column in your table.  Otherwise you will be trying to display objects...which will show as you have in the picture.

[EpicTriffid]

@RandyHayes 

I could kiss you right now! Thank you so much. I've dropped what you put into the ClearCollect statement again so I can use it on multiple screen, and also been able to apply a date so it won't show anything earlier than today or after an abitrary date in the future in case that's every a request. For the life of me I can't seem to get my head around the combination of addcolumns, showcolumns and groupby. It baffles me!

Thankyou again!

[RandyHayes]

@EpicTriffid 

Personally I would skip the collection as you don't need one for this.  If you only need a static snapshot of the table, then you can just assign it to a variable.  If you need your table to be dynamic based on any changes in your app, then a dynamic variable will do.  A collection is overhead you probably don't need.

Add-, Show-, Rename-, Drop-columns are all data shaping tools.  In addition to that, GroupBy, ForAll and Ungroup allow you to shape your data in distinct ways.  ALL of the mentioned functions return tables that have been shaped by the function you use.  These tools are highly important to learn and use as they will rapidly increase your productivity and simplify your design.  For example, what you were originally trying to do by making one collection, then making another with changes to the first and so on, can all be achieved in one step.

Basic concepts:

AddColumns - your data does not have a column you want (static value, calculation, looked up value, another record, another tables, etc), or it has a column you can't use well (i.e. lookup, choice, etc.) and you really just want something simple.  

ShowColumns - your data has more columns than you want to use.  This is helpful in a scenario where you have lots of columns but only want to use a couple or one.  This is not highly needed, but it is helpful if you are passing a table of records to something that expects only certain columns.

RenameColumns - as its name implies, this renames a column.  Helpful function this one!  Good example is in the scenario where you have a combobox and need to set a default.  Your items of the combobox might be something like Distinct (which returns a table with a single column called Result), and you want to match a Title to it.  A record of Title will never match a Record of Result, so you can easily use this function to rename the Title to Result or the other way around.

DropColumns - this is the opposite of the ShowColumns function and will remove particular columns from the table.  This is helpful in scenarios where you have added columns to your table, but other aspects of your app may not like that added column to be in the schema, you can DropColumns on the added column or other.

GroupBy - in it's simple form, takes a table and returns a table that is grouped by the column(s) you specify.  If you provide, for example, a "Title" and a "Recs" for grouping, then the resulting table will have two column, the Title column and another column called Recs that will have all of the records from the original table that match the Distinct values in the Title column.   This is equivalent to doing a Distinct on all table records and then trying to look up each record that has that distinct value.  GroupBy will do it all in one shot.

Ungroup - does the inverse of the GroupBy.  It will take a table and ungroup by a column that you provide.

ForAll - by far the BEST data shaping tool in PowerApps.  Most use it incorrectly as a For/Loop...it is not.  It is a function that will iterate through ANY table you give it and will allow you to provide any record you want as a result in any schema format or...well, pretty much anything.  The sky is the limit with this one and it is very powerful for data shaping.

There are some others, but the above are the powerhouse workers.  

[EpicTriffid]

@RandyHayes 

Absolutely fantastic. Thank you for the simple break down. I've managed to get through all my powerapps thus far minimally using the above functions, either by making sure I shape my data source in the best way, or by somehow cobbling code together from what I see online and managing to make it work. Often times I think the difficulty for me is understanding the flow of the syntax (i.e. what bit relates to what) in the code. 

Thank you again!

[RandyHayes]

@EpicTriffid 

Yes, that can be confusing at first.  The thing is to look from the inside out.

DropColumns(
    SortByColumns(
        AddColumns(
            GroupBy(
                Filter(
                    someRecords,
                    someCriteria
                ),
                "someColumn", "_recs"
            ),
            "_someAdded", SomeValue
         ),
         "_someAdded"
    ),
    "_someAdded"
)

In the above, the first level is the someRecords in the middle.  When looking at the formula you can glean one important point about it...it is a table.  Because filter requires a table.

So from there, the Table is filtered by some criteria.

Filter returns a Table that is the input/datasource (first argument) to the GroupBy.

GroupBy returns a Table with (in this case) two columns - the distinct values of "someColumn" and all the records that match that particular distinct value.

The table returned from GroupBy then becomes the input/datasource (first argument) to the AddColumns function.

AddColumns will add a column called _someAdded to the record schema of the supplied table (remember - it has two columns that were returned from the GroupBy) and return that table.

The resulting table from AddColumns then becomes the input/datasource (first argument) to the SortByColumns function, which will return a table of all the records in the supplied table sorted, in this case, by the "_someAdded" column.

Finally, the table returned from the SortByColumns is supplied as the input/datasource (first argument) to the DropColumns function.  That function will return a table with the "_SomeAdded" column removed.

So, the end result of your formula is a table of data that has been "shaped" how you would want it for the Items property of a control or pretty much for anything you need a table for.

[EpicTriffid]

@RandyHayes 

Ah, that puts in a format that makes alot more sense. I've done some coding in the past, and spent quite alot of time in PowerApps, but it's suprising to me that I've gotten through both the syntax in this, and in things like PowerBI, without an explicit understanding of "Inside Out".  I can't really explain how I've managed to get as far as I have. Thank you again @RandyHayes, your insight is invaluable.

[RandyHayes]

@EpicTriffid 

No problem at all.   By the way, you will get much further if you forget about what you learned doing the coding in the past.  PowerApps is a no-code platform.  If you've done formulas in Excel, then you are better off referencing that knowledge over any coding knowledge as PowerApps was designed around the concept of formulas like Excel.