Help with Data/Table Summarizing into New Collection

[AndrewFitz]

I'm trying to take an existing collection of student records with scores and summarize the data into a new collection. The existing records look something like this:

And I'm trying to summarize it by:

1. Get my my Distinct Date values, then
2. Get my Distinct StudentName values for each date, so I can
3. Get the Average score for each Student, for each Date

So in the end, I have a new table like this:

I've been able to put together a Distinct table for Date or StudentName and add the average score with AddColumns, but I can't figure out how to get all three columns organized and working together. I keep getting "[object Object], [object Object]" values and I'd like to avoid GroupBy because the resulting collection needs to feed into a Line Chart.

I'm stumped. Any help pointing me in the right direction is appreciated.

Andrew

[AndrewFitz · Accepted solution]

Holy Cow! I cannot express how excited I am to figure this out after so many hours of searching, reading, and video watching!

Here is my Formula:

ClearCollect(
    colNewCollection,
    AddColumns(
        ClearCollect(
            colGroupSummary,
            GroupBy(
                colOriginalCollection,
                "Date",
                "StudentName",
                "Summary"
            )
        ),
        "Average Score", 100 *
        Sum(
            Summary,
            Score
        )
        /
        CountRows(Summary)
    )
)

 
I dumped the resulting data into a Line Chart, and added a Filter (to the Chart) based on the item selection of a Gallery made up of a separate Distinct StudentName collection, and voila!