Power Query - how to compare weekly data and identify items that have been processed.

[Sian1]

Hi i have a data model that keep track of weekly progress of items, however i am having issues to figure out how to identify an item has been processed (or cleared out in the subsequent week for the previous week data) see figure 1. As you can see the yellow highlighted items have been cleared out in the week of 2 ie Jan 12. and i would like to have the end results in Fig 2. Appreciate any helps!!

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    lst = Table.SelectRows(Source, each [Report Date] = List.Max(Source[Report Date]))[ID],
    tbl = Table.Group(Source, {"Report Date"}, {{"All", each 
        Table.AddColumn(_, "Item Cleared", (x)=> if not List.Contains(lst, x[ID]) then 1 else null)}}),
    Result = Table.ExpandTableColumn(tbl, "All", {"Follow Up", "Status", "ID", "Item Cleared"})
in
    Result

 

[Sian1]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    lst = Table.SelectRows(Source, each [Report Date] = List.Max(Source[Report Date]))[ID],
    tbl = Table.Group(Source, {"Report Date"}, {{"All", each
        Table.AddColumn(_, "Item Cleared", (x)=> if not List.Contains(lst, x[ID]) then 1 else null)}}),
    Result = Table.ExpandTableColumn(tbl, "All", {"Follow Up", "Status", "ID", "Item Cleared"})
in
    Result

Thanks for the quick response. One more query, a same ID could have two different follow ups and one follow up could have completed next subsequent week and the could remain untouch. In this scenario, will the query be able to identify?

 

[JGordon11]

The code I wrote looks to see if an ID appears in the list of IDs of the latest date in the table (in your example the 1/12/23 group). If it does not appear there it puts a 1 in the Item Cleared column. If it does appear then it puts a null.

 

[Sian1]

Thanks so much