Only keep DateMax row if there is a DateMin row

[ilamoureuse]

Hi!

Following my previous post, I thought that my problem was solved but I didn't think about a case that breaks my vizualization. 

Here is a sample of my data :

Work Item Id		Iteration Path		Attribut		Date		Catégorie de statut
100		Sprint 1		DateMin		2023-04-01		Planned
100		Sprint 1		Date Max		2023-04-15		Planned
100		Sprint 2		Date Min		2023-04-16		Planned
100		Sprint 2		Date Max		2023-04-30		Closed
100		Sprint 3		Date Max		2023-05-01		Closed

I don't want the last row to appear because there is no "DateMin" for the Item 100 in sprint 3. The rule I would like to execute is something like :

For each Work Item Id 

  For each Iteration Path

    if Work Item Id.Attribut = "DateMin" row doesn't exist then filter out Work Item Id.Attribut = "DateMax" row

I would prefer a solution that can be done through Power Query interface rather than a formula, IF THAT'S POSSIBLE. It would allow me to explain the process better to my colleagues. 🙂

Thanks for your help!

[ilamoureuse]

It works ! Thanks a lot @ThxAlot 😁

[ThxAlot · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUNJRCi4oyswrUTAEMl0SS1IVfDPzgEwjAyNjXQMTXQOQeEBOYl5eaopSrA5OTYkVSJoMTfFpMsJuk6EZcZpQbDI2wKfJGJsmU4ifnHPyi0F6YgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Work Item Id" = _t, #"Iteration Path" = _t, Attribut = _t, Date = _t, #"Catégorie de statut" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Work Item Id", Int64.Type}, {"Iteration Path", type text}, {"Attribut", type text}, {"Date", type date}, {"Catégorie de statut", type text}}),

    Grouped = Table.Group(#"Changed Type", {"Work Item Id","Iteration Path"}, {"Grouped", each if List.NonNullCount(List.Difference({"Date Min","Date Max"},[Attribut]))>0 then null else _}),
    #"Selected Rows" = Table.SelectRows(Grouped, each not ([Grouped] is null)),
    #"Expanded Grouped" = Table.ExpandTableColumn(#"Selected Rows", "Grouped", {"Attribut", "Date", "Catégorie de statut"})
in
    #"Expanded Grouped"