Got kind of a confusing one if anyone's able to help would be appreciated. Pretty stuck on what to do

Basically I want to take the below raw data table and look for all the dates per Name that overlap with each other and combine them into one row with the proper Min and Max represented in the single row for the overlapping dates. If there are no overlapping dates just keep the row as well

Raw data table

Got kind of a confusing one if anyone's able to help would be appreciated. Pretty stuck on what to do

Basically I want to take the below raw data table and look for all the dates per Name that overlap with each other and combine them into one row with the proper Min and Max represented in the single row for the overlapping dates. If there are no overlapping dates just keep the row as well

Raw data table

Post query table result needed

For clarity , for the rows that were created from combination I noted which row they received the value from. Those words do not need to be in the result, just wanted to indicate

Please let me know if I have to expand further at all

• dax
• powerquery
• m

2 Answers 2

Paste the code below into the Advanced Editor.
Replace the Source line with your actual data source (eg might have to change the Table name):

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Start Time", type datetime}, {"End Time", type datetime}}),
    
//Group by Name
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {
        
      //add shifted Start Time column to each sorted subgroup  
        {"Shifted", (t)=>
        [a=Table.Sort(t,{"Start Time",Order.Ascending}),
         b=Table.FromColumns(
            Table.ToColumns(a)
              & {{null} & List.RemoveLastN(Table.Column(a,"End Time"),1)},
              type table [Name=text, Start Time=datetime, End Time=datetime, Shifted End Time=nullable datetime]),

    //Overlap exists if start time is less than the shifted end time
         c=Table.AddColumn(b,"Overlap", each [Start Time] < [Shifted End Time], type logical),

    //First row in overlap column will be null so we fill up from the second row to see if they overlap
         d=Table.FillUp(c,{"Overlap"}),

    //Group each subtable by the logical overlap
         e=Table.Group(d,"Overlap", {
             {"Start Time", each List.Min([Start Time]), type datetime},
             {"End Time", each List.Max([End Time]), type datetime}}, 
                GroupKind.Local,(x,y)=>Number.From(y<>true))][e]}
   }),

    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Name", Order.Ascending}}),
    #"Expanded Shifted" = Table.ExpandTableColumn(#"Sorted Rows", "Shifted", {"Start Time", "End Time"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Shifted",{{"Start Time", type datetime}, {"End Time", type datetime}})
in
    #"Changed Type1"

you can try this in the PQ

first create a column based on the start time column

then group by data

at last ,remove useless column