let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
combine = Table.CombineColumns(
Source,
{"Date début", "Date fin"},
(z) => List.Generate(() => z{0}, (x) => x <= z{1}, (x) => Date.AddDays(x, 1)),
"dates"
),
xpand = Table.ExpandListColumn(combine, "dates"),
sort = Table.Sort(xpand, {"EMP ID", "dates"}),
idx = Table.AddIndexColumn(sort, "idx"),
group = Table.Group(
idx,
Table.ColumnNames(idx),
{
{"Date début", (x) => List.Min(x[dates])},
{"Date fin", (x) => List.Max(x[dates])}
},
GroupKind.Local,
(s, c) => Number.From(s[EMP ID] <> c[EMP ID] or (c[dates] - s[dates]) > #duration(c[idx] - s[idx], 0, 0, 0))
),
select_columns = Table.SelectColumns(group, Table.ColumnNames(Source))
in
select_columns
v2
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
continuous = (tbl) =>
[lst = List.Buffer(Table.ToRows(Table.Sort(tbl[[Date début], [Date fin]], "Date début"))),
gen = List.Generate(
() => [i = 0, combine = false, c = lst{0}, result = {}],
(x) => x[i] < List.Count(lst),
(x) =>
[
i = x[i] + 1,
combine = (lst{i}{0} - x[c]{1}) = #duration(1, 0, 0, 0),
c = if combine then {x[c]{0}, lst{i}{1}} else lst{i},
result = x[result] & (if combine then {} else {x[c]})
],
(x) => if x[i] = List.Count(lst) - 1 then x[result] & {x[c]} else null
),
z = #table({"Date début", "Date fin"}, List.Last(gen))][z],
group = Table.Group(Source, {"EMP ID", "Nom", "Libellé motif"}, {{"x", continuous}}),
xpand = Table.ExpandTableColumn(group, "x", {"Date début", "Date fin"})
in
xpand
