Search code examples
daxpowerquerym

Combine rows if the dates exist in another row

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

Name Start Time End Time
Name C 4/1/2025 5:00 AM 4/2/2025 3:50 PM
Name A 1/3/2025 1:00 PM 1/10/2025 1:00 PM
Name A 1/5/2025 1:00 PM 1/20/2025 5:00 PM
Name A 3/2/2025 1:00 PM 3/8/2025 1:00 PM
Name B 2/2/2025 2:00 PM 2/5/2025 3:00 PM
Name C 1/15/2025 1:30 PM 1/19/2025 3:45 PM
Name C 1/12/2025 9:00 AM 1/20/2025 1:00 AM
Name D 1/2/2025 10:00 AM 1/2/2025 1:00 PM
Name A 1/1/2025 5:00 AM 1/15/2025 3:00 PM
Name D 1/2/2025 11:00 AM 1/4/2025 3:00 PM

Post query table result needed

Name Start Time End Time
Name A 1/1/2025 5:00 AM (came from row 9) 1/20/2025 5:00 PM (from row 3)
Name A 3/2/2025 1:00 PM 3/8/2025 1:00 PM
Name B 2/2/2025 2:00 PM 2/5/2025 3:00 PM
Name C 1/12/2025 9:00 AM (from row 7) 1/20/2025 1:00 AM (from row 7)
Name C 4/1/2025 5:00 AM 4/2/2025 3:50 AM
Name D 1/2/2025 10:00 AM (from row 8) 1/4/2025 3:00 PM (from row 10)

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

Solution

  • 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"

    enter image description here