Keeping the latest instance of a value based on criteria

[gvillanueva1009]

Hello - I have the following scenario where I want to keep all the approved requisitions and only keep the latest final disposition date for the Closed requisitions. Can you please assist? Thank you!

Unique ID            Req ID        Title                      Status         Final Disposition Date     As of Date

ABC12312019      ABC123      Goal Digger         Approved                                           1/31/2019

ABC12322019     ABC123       Goal Digger         Approved                                           2/28/2019

ABC12332019     ABC123       Goal Digger         Closed                    3/25/2019            3/31/2019

ABC12342019     ABC123       Goal Digger         Approved                                            4/30/2019

ABC12352019     ABC123       Goal Digger         Approved                                            5/31/2019

ABC12362019     ABC123       Goal Digger         Approved                                            6/30/2019

ABC12372019     ABC123       Goal Digger         Closed                     7/2/2019             7/31/2019

[smpa01]

@gvillanueva1009  is it possible for you to provide the desired solution here based on the data you provided

[gvillanueva1009]

Yes - the data would look like this as the end result: The earlier closed instance would be removed. Thanks!

Unique ID            Req ID        Title                      Status         Final Disposition Date     As of Date

ABC12312019      ABC123      Goal Digger         Approved                                           1/31/2019

ABC12322019     ABC123       Goal Digger         Approved                                           2/28/2019

ABC12342019     ABC123       Goal Digger         Approved                                            4/30/2019

ABC12352019     ABC123       Goal Digger         Approved                                            5/31/2019

ABC12362019     ABC123       Goal Digger         Approved                                            6/30/2019

ABC12372019     ABC123       Goal Digger         Closed                     7/2/2019             7/31/201

[gvillanueva1009]

Unique ID            Req ID        Title                      Status         Final Disposition Date     As of Date

ABC12312019      ABC123      Goal Digger         Approved                                           1/31/2019

ABC12322019     ABC123       Goal Digger         Approved                                           2/28/2019

ABC12342019     ABC123       Goal Digger         Approved                                            4/30/2019

ABC12352019     ABC123       Goal Digger         Approved                                            5/31/2019

ABC12362019     ABC123       Goal Digger         Approved                                            6/30/2019

ABC12372019     ABC123       Goal Digger         Closed                     7/2/2019             7/31/201

[smpa01]

@gvillanueva1009  thanks. Here you go !!!

Power Query _ M_Code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyNjQyNjQyMLRU0oHygAz3/MQcBZfM9PTUIpBwQUFRfllqCpAJRIb6xob6YA2xOmQZYKRvZEGiAc45+cVg7cb6Rqb6ULXGWBxiQopDTPSNDdANMCXFAFMsLjAjxQAzLC4wJzIozPWNYCFhjnBHLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Unique ID" = _t, #"Req ID" = _t, Title = _t, Status = _t, #" Final Disposition Date" = _t, #"As of Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Unique ID", type text}, {"Req ID", type text}, {"Title", type text}, {"Status", type text}, {" Final Disposition Date", type date}, {"As of Date", type date}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Unique ID", "Req ID", "Title"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Status] = "Closed")),
    #"2" = Table.SelectRows(#"Filtered Rows", let latest = List.Max(#"Filtered Rows"[#" Final Disposition Date"]) in each [#" Final Disposition Date"] = latest),
    #"1" = Table.SelectRows(#"Filled Down", each ([Status] = "Approved")),
    Custom1 = #"1"&#"2"
in
    Custom1

Power Query _ M_Result

DAX_Code

Table = 
VAR _1= FILTER(FILTER(Source,Source[Status]="Closed"),Source[ Final Disposition Date]=MAX(Source[ Final Disposition Date]))
VAR _2 =FILTER(Source,Source[Status]="Approved")
VAR _3 = UNION(_2,_1)
RETURN _3

DAX_Result

[gvillanueva1009]

Thank you! I received this error: "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value"

[smpa01]

please check

https://drive.google.com/open?id=10bhcD0ybzsffYtoy6VV4iXActhWcpj99

[gvillanueva1009]

Thank you! It works in your file but when I try it, it doens't. I am pretty sure I am doing something wrong.

[gvillanueva1009]

Thank you! So these options create a table with my desired result?

[smpa01]

yes !!!

[Nathaniel_C]

@gvillanueva1009 ,

Thank you. @ImkeF  is there a way to delete rows based on criteria?

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[Nathaniel_C]

Hi @gvillanueva1009 , 

Depending on big your dataset is, an easy way is to filter out the row in Power Query. On each refresh it will continue to filter those rows. Not Elegant, but quick!

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[Nathaniel_C]

Hi @gvillanueva1009 ,

Not sure how to create this, but the logic seems to be delete unless table[ Status] = "Approved", or [Final Disposition Date ]= MAX(table[Final Disposition Date]. Is that correct? In Power Query? @KenPuls ?

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel