Forum Discussion
data_SWVO
1 year agoRegular Visitor
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-...
- 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
AlienSx
1 year agoSuper User
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- data_SWVO1 year agoRegular Visitor
Thanks a lot, it works!