Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Camstr
Frequent Visitor

Filter based on most recent data

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.

 

Capture.JPG

 

Thanks in advance

1 ACCEPTED SOLUTION
edhans
Super User
Super User

See this pattern. 

This:

edhans_0-1699977313483.png


Becomes this:

edhans_1-1699977330911.png

 

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

2 REPLIES 2
Camstr
Frequent Visitor

Thanks @edhans!

edhans
Super User
Super User

See this pattern. 

This:

edhans_0-1699977313483.png


Becomes this:

edhans_1-1699977330911.png

 

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors