none
Merge Continuous start dates with end dates from row /s above / below RRS feed

  • Question

  • Any suggestions or ideas would be appreciated. I need to combine (start and end ) dates where they are continuous from one row to the next. Thus if End date on row one is 05/05/2018 and the start date is 06/05/2018 (Aus version of dates) then it is continuous.  Thus the two rows can be grouped.  There could be more rows not just two...

    If there is more than a day difference End Date Row1 vs Start Start Row2) then there is no grouping / merging.

    I have tried several things including 2 index columns to merge the data on itself then doing conditional formatting. I.e. If statement. Not quite the solution...

    I have also tried doing a date list of all the dates within Start and End dates but cannot group / merge start date and end date.  I thought of using Max / Min but with no luck...

    Any tips / ideas would be fantastic help!

    Example:

    Original Data
    ID  Start Date End Date
    ABC 1/01/2017 20/02/2017
    ABC 21/02/2017 12/04/2017
    ABC 13/04/2017 2/06/2017
    ABC 5/08/2017 10/08/2017
    ABC 20/08/2017 14/09/2017
    ABC 13/12/2017 7/01/2018
    ABC 7/04/2018 2/05/2018
    ABC 6/05/2018 26/05/2018
    XYZ 5/01/1999 24/02/1999
    XYZ 25/02/1999 16/04/1999
    XYZ 17/04/1999 6/06/1999
    XYZ 5/08/2001 10/08/2001
    XYZ 6/05/2002 26/05/2002
    XYZ 11/08/2002 7/02/2003
    XYZ 8/02/2003 8/02/2003
    XYZ 9/02/2003 5/03/2003


    Required Data
    ID  Start Date End Date
    ABC 1/01/2017 2/06/2017
    ABC 5/08/2017 10/08/2017
    ABC 20/08/2017 14/09/2017
    ABC 13/12/2017 7/01/2018
    ABC 7/04/2005 2/05/2018
    ABC 6/05/2018 26/05/2018
    XYZ 5/01/1999 6/06/1999
    XYZ 5/08/2001 10/08/2001
    XYZ 6/05/2002 26/05/2002
    XYZ 11/08/2002 5/03/2003

    Thanks

    SanPeur

    Tuesday, August 14, 2018 9:57 AM

Answers

  • Dirty version!!

    let
        fxCheck = (t as table) =>
            let
                #"Merged Queries" = Table.NestedJoin(t,{"Indeks"},t,{"Indeks.1"},"Merged",JoinKind.LeftOuter),
                #"Expanded {0}" = Table.ExpandTableColumn(#"Merged Queries", "Merged", {"Start Date", "End Date"}, {"Start Date.1", "End Date.1"}),
                #"Filtered Rows" = Table.SelectRows(#"Expanded {0}", each ([Start Date.1] <> null)),
                #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Marker", each if Number.From([Start Date.1]-[End Date]) = 1 then 0 else [Indeks]    ),
                #"Grouped Rows1" = Table.Group(#"Added Custom", {"Marker"}, {{"All", each _, type table}}, 0),
                #"Added Custom1" = Table.AddColumn(#"Grouped Rows1", "Start", each if [Marker] = 0 then List.Min([All][Start Date]) else List.Min([All][Start Date.1])),
                #"Added Custom2" = Table.AddColumn(#"Added Custom1", "End", each List.Max([All][End Date.1])   ),
                ReverseRows = Table.ReverseRows(#"Added Custom2"),
                #"Removed Duplicates" = Table.Distinct(ReverseRows, {"Start"}),
                ReverseRows2 = Table.ReverseRows(#"Removed Duplicates"),
                #"Removed Columns" = Table.RemoveColumns(ReverseRows2,{"Marker", "All"})
            in
                #"Removed Columns",
    
    
    
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID ", type text}, {"Start Date", type date}, {"End Date", type date}}),
        #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Indeks", 1, 1),
        #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Indeks.1", 0, 1),
        #"Grouped Rows" = Table.Group(#"Added Index1", {"ID "}, {{"All", each _, type table}}),
        #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Niestandardowe", each fxCheck([All])),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"All"}),
        #"Expanded {0}" = Table.ExpandTableColumn(#"Removed Columns", "Niestandardowe", {"Start", "End"}, {"Start", "End"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Expanded {0}",{{"Start", type date}, {"End", type date}})
    in
        #"Changed Type1"


    Tuesday, August 14, 2018 4:09 PM
  • Excel 2010/2013/2016 Power Query (aka Get & Transform)
    Consolidate contiguous dates.
    http://www.mediafire.com/file/rj072bilwi30fck/08_14_18.xlsx/file
    Tuesday, August 14, 2018 4:10 PM

All replies

  • Dirty version!!

    let
        fxCheck = (t as table) =>
            let
                #"Merged Queries" = Table.NestedJoin(t,{"Indeks"},t,{"Indeks.1"},"Merged",JoinKind.LeftOuter),
                #"Expanded {0}" = Table.ExpandTableColumn(#"Merged Queries", "Merged", {"Start Date", "End Date"}, {"Start Date.1", "End Date.1"}),
                #"Filtered Rows" = Table.SelectRows(#"Expanded {0}", each ([Start Date.1] <> null)),
                #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Marker", each if Number.From([Start Date.1]-[End Date]) = 1 then 0 else [Indeks]    ),
                #"Grouped Rows1" = Table.Group(#"Added Custom", {"Marker"}, {{"All", each _, type table}}, 0),
                #"Added Custom1" = Table.AddColumn(#"Grouped Rows1", "Start", each if [Marker] = 0 then List.Min([All][Start Date]) else List.Min([All][Start Date.1])),
                #"Added Custom2" = Table.AddColumn(#"Added Custom1", "End", each List.Max([All][End Date.1])   ),
                ReverseRows = Table.ReverseRows(#"Added Custom2"),
                #"Removed Duplicates" = Table.Distinct(ReverseRows, {"Start"}),
                ReverseRows2 = Table.ReverseRows(#"Removed Duplicates"),
                #"Removed Columns" = Table.RemoveColumns(ReverseRows2,{"Marker", "All"})
            in
                #"Removed Columns",
    
    
    
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID ", type text}, {"Start Date", type date}, {"End Date", type date}}),
        #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Indeks", 1, 1),
        #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Indeks.1", 0, 1),
        #"Grouped Rows" = Table.Group(#"Added Index1", {"ID "}, {{"All", each _, type table}}),
        #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Niestandardowe", each fxCheck([All])),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"All"}),
        #"Expanded {0}" = Table.ExpandTableColumn(#"Removed Columns", "Niestandardowe", {"Start", "End"}, {"Start", "End"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Expanded {0}",{{"Start", type date}, {"End", type date}})
    in
        #"Changed Type1"


    Tuesday, August 14, 2018 4:09 PM
  • Excel 2010/2013/2016 Power Query (aka Get & Transform)
    Consolidate contiguous dates.
    http://www.mediafire.com/file/rj072bilwi30fck/08_14_18.xlsx/file
    Tuesday, August 14, 2018 4:10 PM
  • There's probably enough solutions (Herberts was quite different and thus interesting), but here's another one:

    let 
        fGroup = (Table as table) =>
        let
            AddIndex = Table.AddIndexColumn(Table, "Index", -1, 1)
           ,AddPrevEndDate = Table.AddColumn(AddIndex, "PrevEndDate", each try AddIndex[End Date]{[Index]} otherwise null)
           ,CombineQuestion = Table.AddColumn(AddPrevEndDate, "CombQ", each Number.From([Start Date]-[PrevEndDate])<=1)
           ,Group = Table.Group(CombineQuestion 
                               ,{"CombQ"}
                               ,{{"Start Date", each List.First([Start Date])}
                                ,{"End Date", each List.Last([End Date])}}
                               ,GroupKind.Local
                               ,(X,Y)=>if Y[CombQ] then 0 else 1)
           ,RemoveCols = Table.RemoveColumns(Group,{"CombQ"})
        in
            RemoveCols
    
       ,Source = ChangedTypes
       ,Group = Table.Group(Source, {"ID"}, {"Table", fGroup})
       ,Expand = Table.ExpandTableColumn(Group, "Table", {"Start Date", "End Date"})
       ,ChangeType = Table.TransformColumnTypes(Expand,{{"Start Date", type date}, {"End Date", type date}})
    in
        ChangeType

    I have a question for the OP: can the end date overlap with the next rows start date or even the next rows end date? If it can, you should say so. 

    Wednesday, August 15, 2018 7:39 AM
  • Hi,  thanks to all for their ideas and work.  It was unintentional that there was an overlap of dates.  Yet not entirely  impossible.  Definitely something to factor in.  Cheers  S P
    Friday, September 7, 2018 9:50 PM