Max date for each category with other values

[AshkanDRB]

Hi everyone
I have showed my need in this image.
Thanks in advance for your help.

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"ID"}, {{"Count", each Table.Max(_,"Date")}}),
    #"Expanded Count" = Table.ExpandRecordColumn(#"Grouped Rows", "Count", {"Date", "Status"}, {"Date", "Status"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Count",{{"ID", type text}, {"Date", type date}, {"Status", type text}})
in
    #"Changed Type"

Hope this helps.

[Ashish_Mathur · Accepted solution]

Hi,

Just in case you want a solution using measures, then refer to my solution in the attached PBI file.

Hope this helps.

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"ID"}, {{"Count", each Table.Max(_,"Date")}}),
    #"Expanded Count" = Table.ExpandRecordColumn(#"Grouped Rows", "Count", {"Date", "Status"}, {"Date", "Status"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Count",{{"ID", type text}, {"Date", type date}, {"Status", type text}})
in
    #"Changed Type"

Hope this helps.

[AshkanDRB]

Hi Ashish 
Thanks. Your both solutions work perfect.

[Ashish_Mathur]

You are welcome.

[belvoir99]

You can create a calculated table (Modeling > New Table):

SumTable = 
SUMMARIZECOLUMNS(
    'Table'[ID],
    "Date", MAX('Table'[Date]),
    "Status", 
    VAR MaxDate = MAX('Table'[Date])
    RETURN
    CALCULATE(VALUES('Table'[Status]), 'Table'[Date] = MaxDate)
)