Forum Discussion
Grouping overlapping contracts with first/last dates
- Anonymous6 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_CEin this case the result seems what you expect, but I suggest you test other situations as well
Interesting problem. Does it have to be done in Power Query? If this data would reside in SQL server then it would be much easier to do the logic there.
In your sample data some durations are embedded in others (for example row 3 for ID 100 is fully covered by row 2). Is that intended?
- Anonymous6 years agoNot applicable
Thanks for your response lbendlin .
Currently the data is not able to be supplied via SQL server, but it is my intention to move to this in the longer term where, as you point out, the logic would be much easier.
Also, some contracts are embedded within earlier contracts, due to the way the contracts have been issued historically, so the sample data is intended to replicate this issue.