Filter based on most recent data

[Camstr]

Hello,

I have a fictional database that contains school test results by class and subject. 

The database is updated with the latest results and retains all previous records.

I want to make a Power BI report where I can visualise the latest average grade and other information by class and subject based upon the most recent test date.

I want to use Power Query to clean and prepare my data, to show most recent test details, prior to working with the data in Power BI desktop.

I need the report to update everytime new tests are taken, these will be future unknown dates.

See attached screenshot of what I have and what I want.

Thanks in advance

[Camstr]

Thanks @edhans!

[edhans · Accepted solution]

See this pattern. 

This:

Becomes this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjZU0lHyTSzJAFKmBkDCyBRIGBroG+obGRgZK8XqQNW45qXnZBaDlYFUGBtAlJmiKYMZZQYzykTfBKdJ5hZIJhlBlcUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Class = _t, Subject = _t, #"Average Grade" = _t, #"Student Count" = _t, #"Test Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Test Date", type date}, {"Student Count", Int64.Type}, {"Average Grade", Int64.Type}}),
    #"Grouped Rows" = 
        Table.Group(
            #"Changed Type", 
            {"Class", "Subject"}, 
            {
                {
                    "All Rows", 
                    each 
                    let
                        varMaxDate = List.Max(_[Test Date])
                    in
                        Table.SelectRows(_, each [Test Date] = varMaxDate), 
                    type table [Class=nullable text, Subject=nullable text, Average Grade=nullable number, Student Count=nullable number, Test Date=nullable date]
                }
            }
        ),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All Rows", {"Average Grade", "Student Count", "Test Date"}, {"Average Grade", "Student Count", "Test Date"})
in
    #"Expanded All Rows"

It groups everything by the class and subject and turns everything in to a nested table.

It then finds the max Test Date for each class/subject combo and filters all of those records for that date. Then it expands the nested table.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.