Forum Discussion
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-...
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
Hi data_SWVO, check this:
Edited
Output
let
fn_ShiftRows =
(tbl as table, col as text, shift as nullable number, optional newColName as text, optional _type as type) as table =>
//v 3. parametri zadaj zaporne cislo ak chces posunut riadky hore, kladne ak dole, 4. je nepovinny (novy nazov stlpca), 5. je nepovinny typ
let
a = Table.Column(tbl, col),
b = if shift = 0 or shift = null then a else if shift > 0
then List.Repeat({null}, shift) & List.RemoveLastN(a, shift)
else List.RemoveFirstN(a, shift * -1) & List.Repeat({null}, shift * -1),
c = Table.FromColumns(Table.ToColumns(tbl) & {b}, Table.ColumnNames(tbl) &
( if newColName <> null then {newColName} else
if shift = 0 then {col & "_Duplicate"} else
if shift > 0 then {col & "_PrevValue"}
else {col & "_NextValue"} )),
d = Table.TransformColumnTypes(c, {List.Last(Table.ColumnNames(c)), if _type <> null then _type else type any})
in
d,
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc3BCoAgEATQXxHPLjirVv5F9/DYtaD+H0o3SchuA/tmdln0vB7nvikobTRoJLaMOzpLQ86sk+kizggE/lPl4ERNOfsWsaBYpzhKoYecHN6l0CJXUBCV/8HXxkdRaKZg+yoQ0DJ+XqYL", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Person = _t, #"Start Date" = _t, #"End Date" = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}}, "sk-SK"),
GroupedRows = Table.Group(ChangedType, {"Person"}, {{"All", each
[ //_tbl = GroupedRows{[Person="Person 1 "]}[All],
_tbl = _,
SortedRows = Table.Sort(_tbl,{{"Start Date", Order.Ascending}}),
Ad_EndDatePrevValue = fn_ShiftRows(SortedRows, "End Date", 1, null, type date),
Ad_Index = Table.AddIndexColumn(Ad_EndDatePrevValue, "Index", 0, 1, Int64.Type),
Ad_GroupHelper = Table.AddColumn(Ad_Index, "GroupHelper", each try if Duration.Days([Start Date] - [End Date_PrevValue]) <= 30 then [Index] else [Index] + 1000000 otherwise 0, Int64.Type),
GroupedRows1 = Table.Group(Ad_GroupHelper, {"Person", "GroupHelper"}, {{"All", each _, type table}, {"Start Date", each List.Min([Start Date]), type date}, {"End Date", each List.Max([End Date]), type date}}, GroupKind.Local,
(x,y)=> try Number.From( y[GroupHelper] - x[GroupHelper] > 1) otherwise false ),
RemovedColumns = Table.RemoveColumns(GroupedRows1,{"GroupHelper", "All"})
][RemovedColumns], type table}}),
Combined = Table.Combine(GroupedRows[All])
in
Combined
