Get a Status from related table given many Filters and most recent date

Hello Anonymous 

 

here a solution in Power Query. The first two steps are to create data example, the third step is a Table.AddColumn that filters the Mail-Table, sorts it and choose the first row of the column "Status"

let
    Mail = 
    let
        Source = #table
        (
            {"Name","Type","Date","Status"},
            {
                {"A","X","01.01.20","on"},	{"A","X","08.02.20","off"},	{"A","Y","03.05.20","on"},	{"A","Y","01.12.21","neutra"},	{"B","Z","01.01.01","on"},	{"B","X","08.01.20","off"}

                        }
        ),
        TransDate = Table.TransformColumns
        (
            Source,
            {
                "Date",
                each Date.From(_,"de-DE"),
                type date
            }

        )
    in
	    TransDate,
    

    Users= #table
    (
        {"Name"},
        {
            {"A"},	{"B"}

        }
    ),

    AddLatestStatus = Table.AddColumn
    (
        Users,
        "Most recent status",
        (add)=> Table.Sort(Table.SelectRows(Mail, each [Name]= add[Name]), {"Date", Order.Descending})[Status]{0}

    )
in
    AddLatestStatus

 

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy