Find the Max Date status for each Year(Ex:2020,2021,2022) Using 3 or more Columns in Power Query

[Anony_mous]

Hi All,

I am new to power BI I got a requirement where I have columns Course Code, Training Name, Email, Status, Date 

Sample Data:

Course Code             Training Name      Email                          Status                 Date                Modules
175HF-IN                   ABRA                    xyz@gmail.com          Completed         2/2/2022        Safety
175HF-IN                   ABRA                    xyz@gmail.com           Enrolled            11/26/2020     Safety
185JT-IN                    HURI                     bcd@gmail.com          Completed        10/5/2021       Human Rights
185JT-IN                    HURI                     bcd@gmail.com          In progress         9/8/2021         Human Rights
256BT-IN                    Silver                    qwe@gmail.com          Completed         12/11/2022    Environment

as we can see in the data the 1st and 2nd rows  the Course Code,Training Name,Email is same but Date is different for both the rows .Meanwhile year is also not same so it has to capture both the rows data. Likewise the data in the 3rd and 4th rows Course Code,Training Name,Email is same but  year is same in both 3rd and 4th rows so it has to capture the latest Date data that is 10/5/2021 instead of 9/8/2021.
Expected Result:

Course Code             Training Name      Email                          Status                 Date                Modules
175HF-IN                   ABRA                    xyz@gmail.com          Completed         2/2/2022        Safety
175HF-IN                   ABRA                    xyz@gmail.com           Enrolled            11/26/2020     Safety
185JT-IN                    HURI                     bcd@gmail.com          Completed        10/5/2021       Human Rights

256BT-IN                    Silver                    qwe@gmail.com          Completed         12/11/2022    Environment

All suggestions are welcome thank you in advance!!

Regards,

Anony_mous

[Anony_mous]

Hi thanks for your reply but it's not working as expected.I need to remove rows which are not meeting the conditions  in the same table by using power query or in another table using dax. please go through the question once again and try to reply back. 
Regards,

Anony_mous

[v-shex-msft · Accepted solution]

Hi @Anony_mous,

Sure, here is the M query version to group and filter records, you can try it if helps.

Full query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdA9D4IwEAbgv2I6o6VNQNwEgwEHB9CJMCBUJOkHlorir7cVJxMScsO9l1yeXJtlAK2daL+Mj8ACfpD4ur2G97ZmRUNXpWB63gnWUqJIpTOGumyMdUyLK1EDyK05RsiloPRLIASxaxD7D/Gcw2lEonMS63Ypq8lDkA0dgyCz/mAFXyRNfVPdTCrmi1aKWpKu09MGepMWdtzgZ6UN7YnU4f4k04dhaF44/lHI+0YKzghXIM8/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Course Code" = _t, #"Training Name" = _t, Email = _t, Status = _t, Date = _t, Modules = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Course Code", type text}, {"Training Name", type text}, {"Email", type text}, {"Status", type text}, {"Date", type date}, {"Modules", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date])),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Course Code", "Training Name", "Email", "Year"}, {{"Context", each Table.LastN(_, 1), type table [Course Code=nullable text, Training Name=nullable text, Email=nullable text, Status=nullable text, Date=nullable date, Modules=nullable text, Year=number]}}),
    #"Expanded Context" = Table.ExpandTableColumn(#"Grouped Rows", "Context", {"Status", "Date", "Modules"}, {"Context.Status", "Context.Date", "Context.Modules"})
in
    #"Expanded Context"

Result:

Comment:

In above sample code, I add a custom column to extract the year from data fields.
Then I used Table.Group function to group records based on "Course Code", "Training Name", "Email", "Year" feilds and nest the Table.LastN function to filter records.

Regards,

Xiaoxin Sheng

[v-shex-msft]

Hi @Anony_mous,

You can try to use the following measure formula on ‘visual level filter’ to filter displayed visual records:

formula =
VAR currDate =
    MAX ( 'Table'[Date] )
VAR _lastDateByGroup =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALLSELECTED ( 'Table' ), YEAR ( 'Table'[Date] ) = YEAR ( currDate ) ),
        VALUES ( 'Table'[Course Code] ),
        VALUES ( 'Table'[Training Name] ),
        VALUES ( 'Table'[Email] )
    )
RETURN
    IF ( currDate = _lastDateByGroup, "Y", "N" )

Regards,

Xiaoxin Sheng