Find a value in Grouped Data and Delete Rows

[Richard_Halsall]

Hi

I am needing help with the following scenario. I have grouped data by ID and date with tasks for that day, I am trying to delete all rows in the group IF there is a taskType called 'Rest Day' (this task should not be delete) else do not delete any rows

Grouped Data

For this day you would delete both rows with the taskType Working Hours to leave a single row with taskType 'Rest Day'

For this day you would delete no rows

For this day you would delete no rows

taskType can contain values Working Hours, Rest Day, Standby Onsite & Standby Offsite

Any advice is much appreciated. Thanks

[dufoq3 · Accepted solution]

Hi @Richard_Halsall, for future requests provide sample data in usable format (not as a screenshot!)

Before

After

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjbQNzTSNzIwMlHSUQpKLS5RcEmsBDITgdhQKVYHTUV4flF2Zl66gkd+aVExVJkBKcoMzPUNDEHKTKmizBRJGabrYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Site_Start_Date = _t, taskType = _t, Contractor = _t, RestDayFlag = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Site_Start_Date", type date}, {"RestDayFlag", Int64.Type}}),
    GroupedRows = Table.Group(ChangedType, {"Site_Start_Date"}, {{"All", each _, type table}, {"T", each if List.Contains([RestDayFlag], 1) then Table.SelectRows(_, each [RestDayFlag] = 1) else _, type table}}),
    CombinedT = Table.Combine(GroupedRows[T])
in
    CombinedT

[BeaBF]

@Richard_Halsall Hi! Can you paste your M code of the advanced editor?

BBF