Forum Discussion

data_SWVO's avatar
data_SWVO
Regular Visitor
1 year ago
Solved

Group by Start Date and End Date using a condition

Hi everyone!   I am trying to use the function group by to get below source dataset   Source data Person Start Date End Date Person 1  1-7-2021 30-6-2022 Person 1  1-7-2022 31-12-...
Need Help
  • AlienSx's avatar
    AlienSx
    1 year ago
    let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    rows = List.Buffer(Table.ToRecords(Source)), 
    gen = List.Generate(
        () => [i = 0, combine = false, c = rows{0}, s = {}],
        (x) => x[i] < List.Count(rows),
        (x) => [
            i = x[i] + 1, 
            combine = (rows{i}[Person] = x[c][Person]) and (rows{i}[Start Date] - x[c][End Date] <= #duration(30, 0, 0, 0)), 
            c = if combine then x[c] & [End Date = rows{i}[End Date]] else rows{i},
            s = if combine then x[s] else x[s] & {x[c]}
            ], 
        (x) => if x[i] = List.Count(rows) - 1 then x[s] & {x[c]} else x[s]
    ),
    res= Table.FromRecords(List.Last(gen))
in
    res
PhilipTreacy's avatar
PhilipTreacy
Super User
1 year ago

Hi data_SWVO 

 

I'm not following the logic used to get the result.  I don't follow what you mean by intervals of 30 days and how that applies to the data.  Please try explaining again and illustrate how you are turning the source data into the result.

 

Regards

 

Phil

data_SWVO's avatar
data_SWVO
Regular Visitor
1 year ago

Thanks for your quick response Phil. It's nice that you asked for an explanation, because I actually have no experience on this forum. I will try to explain it further.

 

Let's take person 1 as an example: The difference between the Start Date of row 2 (1-7-2022) compared to the End Date of row 1 (30-6-2022) is less than 30 days. These rows must be merged into 1 row with the Start Date of row 1 (1-7-2021) and the End date of row 2 (31-12-2022).

 

The difference between the Start Date of row 3 (1-2-2023) and the end date of row 2 (31-12-2022) is more than 30 days. Therefore, the row 3 may not be merged with rows 1 and 2 and the Start Date and End Date remain the same.

 

Is it clearer to you this way?

 

Best regards, Jolanda