Filter or new table to display latest comment

[StuartSmith]

I have a table called "Export" with various columns including, "ID", "Budget Code", "Comment" & "Comment Date" with a similar layout to below...

Building ID	Budget Code	Comment	Comment  Date
1	50	Blah	01/01/2022
1	60	Blah, Blah	02/01/2022
2	30
2	40
3	20	Blah	01/01/2022

and so on.

I need to somehow, filter or create a new table to display similar to below...

Building ID	Budget Code	Comment	Comment Date
1	60	Blah, Blah	02/01/2022
2	30
3	20	Blah	01/01/2022

so the criteria needs to be...

If a "Building ID" has multiple rows and comments, keep the row with the most recent comment date.
If a "Building ID" has multiple rows and no comments, keep any row.
If a "Building ID" has 1 row, keep that row, whether there are comments or not

I think that covers it and havent missed anything out.

Thanks in advance

[StuartSmith]

Thanks guys, will take a look shortly and get back to you with any questions.

[ribisht17]

You are welcome !!

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI1ABJOOYkZQMrAUBeIjAyMjJRidSDSZjBpHQWYIiMURUZAIWOQIiBSgIuYoIgYA1lGOOyJBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Building ID" = _t, #"Budget Code" = _t, Comment = _t, #"Comment  Date" = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Building ID"}, {{"All", each Table.Max(_,"Comment  Date")}}),
    #"Expanded All" = Table.ExpandRecordColumn(#"Grouped Rows", "All", {"Budget Code", "Comment", "Comment  Date"}, {"Budget Code", "Comment", "Comment  Date"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded All",{{"Comment  Date", type date}})
in
    #"Changed Type"

Hope this helps.

[ribisht17]

Hope this satisfies your requirement, if yes, kindly mark the answer as correct so that it can help others as well

Regards,

Ritesh

Mark my post as a solution if it helped you| Munde and Kudis (Ladies and Gentlemen) I like your Kudos!! !!
My YT Channel Dancing With Data !! Connect on Linkedin !!Power BI for Tableau Users 

[ribisht17 · Accepted solution]

@StuartSmith 

Step 1

RANK LATEST = RANKX(FILTER( ALL( Sheet2),Sheet2[Building ID]=max(Sheet2[Building ID])),CALCULATE(min(Sheet2[Comment Date])))

Set RANK LATEST to 1

Step 2

Max Budget Code Diff = CALCULATE(max(Sheet2[Budget Code]),all(Sheet2),Sheet2[Building ID]=max(Sheet2[Building ID])) - min(Sheet2[Budget Code])

Filter this to 0

Regards,

Ritesh