Grouping data

(0) Share

Report

Posted on by EpicTriffid

364

Morning all,

I'm sure that I don't understanding GroupBy and AddColumns enough to be doing this, so to save myself running in circles and throwing stuff at the wall to see what sticks, I thought I would seek some guidance. I have a sharepoint list that I'm filtering into a collection to remove all blank rows. the resulting data looks like this:

Now what I need is create a list of distinct Items, where item selected to keep has the closest date to today. So far I've tried to group them by Item but i get [Object object] errors when i try to display them in a data table:

ClearCollect(
 calendarCol,
 GroupBy(
 AddColumns(
 Filter(
 calPre,
 Not(IsBlank(FAQ_x0020_Item_x0020_Name))
 ),
 "Itm",
 Text('FAQ Item Name'.Value),
 "Mon",
 First(
 Sort(
 Filter(
 calPre,
 Month >= Today()
 ),
 Month,
 Ascending
 )
 ).Month
 ),
 "Itm",
 "Mon"
 )
)

I feel like I might be almost there but I can't figure out how to make GroupBy and AddColumns works.

Any help would be appreciated

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

WarrenBelz 153,034 Most Valuable Professional on at

Like (0)

Report
Hi @EpicTriffid ,

You need to add a final column to the GroupBy() to take all the grouped fields (or it will assume Mon is it). Also Month is not a good name for a Field as it is a Reserved Word in Power Apps.

ClearCollect( calendarCol, GroupBy( AddColumns( Filter( calPre, !IsBlank(FAQ_x0020_Item_x0020_Name) ), "Itm", 'FAQ Item Name', "Mon", First( Sort( Filter( calPre, Month >= Today() ), Month, Ascending ) ).Month ), "Itm", "Mon", "Data" ) )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
EpicTriffid 364 on at

Like (0)

Report
Hi @WarrenBelz,

Thanks for the reply. The above makes sense in terms of it not having anything to take all the grouped fields. However, while it is now giving me a distinct list with dates, all the dates are set to tomorrow (13/04/2021), like below:

Thinking that my method of getting only those items with the soonest occurring date was incorrect, i set a variable to work out a month from now and added that into my filter (I also changed the fields name of Itm and Mon to make it easier to understand:

Set(nextMonth, DateAdd(Today(), 1, Months)); ClearCollect( calendarCol, GroupBy( AddColumns( Filter( calPre, !IsBlank(FAQ_x0020_Item_x0020_Name) ), "FAQItem", Text('FAQ Item Name'.Value), "dateMonth", First( Sort( Filter( calPre, And(Month >= Today(), Month <= nextMonth) ), Month, Ascending ) ).Month ), "FAQItem", "dateMonth", "Data" ) )

However, this gives the same thing of tomorrows date for all items. I would assume I've made an error in using First but I'm not how to get around it as i can't seem to return any date value without wrapping date in First.

Was this reply helpful? Yes No
Verified answer

WarrenBelz 153,034 Most Valuable Professional on at

Like (0)

Report
Thanks @EpicTriffid ,

I see what you are doing now - just had to get my mind around the logic. Firstly if you use a data table (rather than a gallery), you will need to delete the "Data" field as it will be a table. As well, what type of field is 'FAQ Item Name'?

If it is a Single line of Text, you can do this

ClearCollect( calendarCol, AddColumns( GroupBy( RenameColumns( Filter( calPre, !IsBlank(FAQ_x0020_Item_x0020_Name) ), "FAQ_x0020_Item_x0020_Name", "Item" ), "Item", "Data" ), "Mon", First( Sort( Data, Month ) ).Month ) )

If it is a Choice field, you will need this

ClearCollect( calendarCol, AddColumns( GroupBy( AddColumns( Filter( calPre, !IsBlank(FAQ_x0020_Item_x0020_Name.Value) ), "Item", FAQ_x0020_Item_x0020_Name.Value ) "Item", "Data" ), "Mon", First( Sort( Data, Month ) ).Month ) )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
WarrenBelz 153,034 Most Valuable Professional on at

Like (0)

Report

Hi @EpicTriffid ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
EpicTriffid 364 on at

Like (0)

Report

Hi @WarrenBelz Yes, It did thankyou! I'll mark it as a solution now!

Was this reply helpful? Yes No