Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Grouping overlapping contracts with first/last dates

I have an export of contract data where overlapping contracts need to be grouped into continuous dates, with the first/last dates for each group. Individual IDs may have more than one group of contra...
  • Anonymous's avatar
    Anonymous
    6 years ago

    Here a complete solution (to test, of course, with more significant data).

     

     

    let
    
        #"Raggruppate righe" = Table.Group(yourTable, {"ID"}, {{"Conteggio", each Table.RowCount(_), type number}, {"startend", each CSEsorted( _[Contract_Start], _[Contract_End])}}),
        #"Tabella startend espansa" = Table.ExpandListColumn(#"Raggruppate righe", "startend"),
        #"Valori estratti" = Table.TransformColumns(#"Tabella startend espansa", {"startend", each Text.Combine(List.Transform(_, Text.From), ":"), type text})
    in
        #"Valori estratti"

     

     

    this modified function does not rely on the order of identifiers.

     

     

    let 
    CS_CE=(start as list, end as list)=>
    let
    
    startend=List.Sort(List.Zip({start,end}),(x,y)=>Value.Compare(x{0},y{0})),
    unionInt=List.Accumulate(startend, [CS={List.Min(start)},CE={List.Min(start)}], 
                                                 (s,c)=> [CS= if c{0}>List.Last(s[CE]) then s[CS]&{c{0}} else s[CS],
                                                          CE= if c{0}>List.Last(s[CE]) then s[CE]&{c{1}} else 
                                                      if c{1}>List.Last(s[CE]) then List.RemoveLastN(s[CE],1)&{c{1}} else s[CE]])
    in
    
    List.Zip(Record.ToList(unionInt))
    
    in CS_CE

     

     

     

    in this case the result seems what you expect, but I suggest you test other situations as well