Forum Discussion
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' ...
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
