Forum Discussion
Super UserHow to use List.Generate to find continous ranges?
Hello, jaryszek you can do everything in 1 step using Table.Group. I would not bother with List.Generate. Here is basic idea: sort by names and dates, then group as long as names are equal and dates are back to back. The latter is a little bit tricky but doable: add index column and compare duration in days between first and current row and difference in values of index column. Key is GroupKind.Local argument of Table.Group. Sorting order is also super important. I grouped rows for you that each row is a single vacation event. Do the rest yourself (add end date as max date, working days calculation etc as you usually do in Table.Group). Hope this was helpful.
let sort = Table.Sort(#"Table1 (3)",{{"Name", Order.Ascending}, {"Vacation Date", Order.Ascending}}), idx = Table.AddIndexColumn(sort, "Index", 1, 1, Int64.Type), g = Table.Group( idx, {"Name", "Vacation Date", "Index"}, {"all", each _}, GroupKind.Local, (s, c) => Byte.From( (s[Name] <> c[Name]) or (Duration.Days(c[Vacation Date] - s[Vacation Date]) <> (c[Index] - s[Index])) ) ) in g
Super Userjaryszek no, don't do anything to what's inside Byte.From. Inside is in fact a condition to start new group. But Table.Group requires 0/1 as a result of this function so we have to use either Number.From or Byte.From which converts logical to 1 or 0.
If you want to calculate the number of working days then add another aggregated column withing Table.Group:
{"wd", (x) => List.Count(List.Select(x[Vacation Date], (d) => not List.Contains({5, 6}, Date.DayOfWeek(d)))}
with regard to s and c: I don't know how you can debug this. "s" stands for "state" (or first row of this group). "c" stands for "current" - current row of the group and you evaluate (s, c) => for being equal to 1 or 0.
And you can work with only table fields mentioned as column names in Table.Group 2nd argument. We have a condition with index, vacation date and name columns. So that we have to mention them as a list argument of Table.Group. From the other side if you evaluate just 1 field, then you can mention it's name in Table.Group without curley brackets and don't use [ ] in (s, c). List this: Table.Group(source, "column_name", ..., (s, c) => Byte.From(c - s >= 10))
Super UserThank you very much AlienSx
One more thing.
If i want to add agregator for Names to count people vacations, how can i do this?
So for Smith it will have Vacation No = 1 and 2 ,
For Lisa = 1
So each Table with specific name is a number + 1.
I tried with:
let
Source = Table1,
Index = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
g = Table.Group(
Index, {"Name", "Vacation Date", "Index"},
{
{"All", each _},
{"wd", each Table.RowCount(_)}
},
GroupKind.Local,
(s, c) => Byte.From(
(s[Name] <> c[Name]) or
(Duration.Days(c[Vacation Date] - s[Vacation Date]) <> (c[Index] - s[Index]))
)
)[[All], [wd]]
in
g
but what i got is number of rows for each table, i just want to have counter across tables for specific names.
Best,
Jacek