Forum Discussion

ilamoureuse's avatar
ilamoureuse
Regular Visitor
3 years ago
Solved

Only keep DateMax row if there is a DateMin row

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!

 

Need Help
  • ThxAlot's avatar
    ThxAlot
    3 years ago
    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"

2 Replies

  • ThxAlot's avatar
    ThxAlot
    Icon for Super User rankSuper User
    3 years ago
    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"