Forum Discussion

Richard_Halsall's avatar
Richard_Halsall
Helper IV
1 year ago
Solved

Find a value in Grouped Data and Delete Rows

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

Need Help
  • dufoq3's avatar
    dufoq3
    1 year ago

    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

2 Replies

  • dufoq3's avatar
    dufoq3
    Community Champion
    1 year ago

    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