how do I calculate total duration from multiple rows by group

Good day yishenhui,

My interpretation of your question is that you wish to calculate, for each group, the duration in days between the maximum end date and minimum start date and your dates are in mm/dd/yyyy format. If this is the case the answer for A would be 9 so I’m not sure my interpretation is correct.

Even if my interpretation is not precise, an approach such as this may provide a solution.

1. Group by group, using the advanced options to add two columns, each summarised.
   1. Min Start
   2. Max End
2. Calculate the days between max end and min start.

The following code achieves that.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"group", type text}, {"Start date", type datetime}, {"End Date", type datetime}}),
    #"Grouped Rows" = Table.Group(
       #"Changed Type", 
       {"group"}, 
       {
          {"Min Start", each List.Min([Start date]), type nullable datetime}, 
          {"Max End", each List.Max([End Date]), type nullable datetime}
       }
    ),
    #"Add Days" = Table.AddColumn(
       #"Grouped Rows", 
       "Days", 
       each Duration.Days( [Max End] - [Min Start] ), 
       Int64.Type
    )
in

    #"Add Days"

Here is my data (at the step “Changed Type”)

Here is my result (my date format is dd/mm/yy)

Hope this helps.