Forum Discussion

Moliems's avatar
Moliems
Frequent Visitor
3 years ago
Solved

Max Date within a Group + extra condition

Hi there, could anyone help me in Power Query to pick the row for each student that has the max date below?

 

I have an extra issue that for some students there is more than one max date, so i would then choose the status column that has the earliest letter in the alphabet!

 

Example data below with required output.  Thank you in advance for any help!

 

Student

Name        Date           Status
Student 1 12/01/2021 A
Student 1 10/01/2021 C
Student 1 12/01/2021 B
Student 2 09/01/2021 B
Student 2 08/01/2021 C
Student 2 09/01/2021 A
Student 3 01/01/2021 A
Student 3 03/01/2021 B
Student 3 04/01/2021 C
Student 3 18/01/2021 D

Desired Output:
Student

Name        Date           Status
Student 1 12/01/2021 A
Student 2 09/01/2021 A
Student 3 18/01/2021 D

Need Help
Tips and Tricks
  • AlienSx's avatar
    AlienSx
    3 years ago

    Hi, Moliems 

    let
    Source = you_table,
    gr = 
        Table.Group(
            Source, "Name", 
            {"r", 
            each 
                Table.FirstN(
                    Table.Sort(_, {{"Date", Order.Descending}, {"Status", Order.Ascending}}), 
                    1
                )
            }
        ),
    z = Table.Combine(gr[r])
in
    z

2 Replies

  • AlienSx's avatar
    AlienSx
    Super User
    3 years ago

    Hi, Moliems 

    let
    Source = you_table,
    gr = 
        Table.Group(
            Source, "Name", 
            {"r", 
            each 
                Table.FirstN(
                    Table.Sort(_, {{"Date", Order.Descending}, {"Status", Order.Ascending}}), 
                    1
                )
            }
        ),
    z = Table.Combine(gr[r])
in
    z