Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jaryszek
Post Patron
Post Patron

How to use List.Generate to find continous ranges?

Hi Guys,

 

my input data:
NameVacation Date

Smith09.03.2023 00:00:00
Smith06.08.2023 00:00:00
Smith07.08.2023 00:00:00
Smith08.08.2023 00:00:00
Smith09.08.2023 00:00:00
Lisa01.07.2023 00:00:00
John14.06.2023 00:00:00
John15.06.2023 00:00:00
Anne05.04.2023 00:00:00
Anne06.04.2023 00:00:00
Anne07.04.2023 00:00:00
Anne08.04.2023 00:00:00
Anne09.04.2023 00:00:00
Anne18.05.2023 00:00:00
Anne19.05.2023 00:00:00
Anne20.05.2023 00:00:00
Anne21.05.2023 00:00:00
Anne22.05.2023 00:00:00
Anne05.09.2023 00:00:00

 

and now i grouped rows:

 

 

 

 

let
    Source = #"Table1 (3)",
    GroupedRows = Table.Group(Source, {"Name"}, {{"Grp", each 
        let a = _, 
        b = Table.AddIndexColumn(a, "Index", 0, 1, Int64.Type),
        counter = 0,
        c = Table.AddColumn(b, "Vacation No", each
                 let   
                 d = b[Vacation Date],
                 CounterState = List.Generate(
                        () => [IndexNo=1, CounterValue = 1, PrevDate=d{1}],
                        each [CounterValue] < 5,
                        each [
                            CounterValue = 1,
                            PrevDate = d{1}
                        ],
                        each [CounterValue]
                    )
                in CounterState
                )
        in c}})
in
    GroupedRows

 

 

 

so what i want to do is for each person generate list of continuous ranges. 

Output should be:

NameVacation NoVacation From DateVacation End DateNumber of Workdays
Smith109.03.202309.03.20231
Smith207.08.202309.08.20233
John114.06.202315.06.20232
Anne105.04.202307.04.20233
Anne218.05.202322.05.20233
Anne305.09.202305.09.20231


so as you can see i need to use List.Generate to add record with Vacation No, Workdays, Vacation From Date and Vacation End Date. 

I stacked because List.Generate is creating a list inside each row, but I have issues with:
1) How to set up start of list:
d = b[Vacation Date] = i am looping always through whole list which is not necessary, I want to just start from specific row.
2) how to use date time functions and duration to specify if days are next to each other?
In this step, I want to build lists only for specific rows and check if ranges are continuous (if day diff <> 1 , then it is not continuous range and should reset whole loop and start from specific date)

Will be grateful for any help,
Best,
Jacek

1 ACCEPTED SOLUTION
AlienSx
Super User
Super User

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

View solution in original post

8 REPLIES 8
AlienSx
Super User
Super User

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

Thank you very much @AlienSx ! 

It is very nice solution. 
If i do not want to have dates on weekends here (like Sunday or Saturaday) i would add somethins like here?:
not List.Contains({5,6}, Date.DayOfWeek(c[Vacation Date]))

 

(s, c) => Byte.From(
(s[Name] <> c[Name]) or
(Duration.Days(c[Vacation Date] - s[Vacation Date]) <> (c[Index] - s[Index])) and
not List.Contains({5,6}, Date.DayOfWeek(c[Vacation Date]))
)

So it means that 06.07.2023 will be removed from table, it is weekend day...

and one more question, how to debug state and current state? Sometimes it is hard to imagine what is happening there...

Best,
Jacek

@jaryszek 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)) 

Thank 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 


but what i got is number of rows for each table, i just want to have counter across tables for specific names. 

Best,
Jacek

@jaryszek maybe group the resulting table by name and add index column

Table.Group(result, "Name", {"vac", each Table.AddIndexColumn(_, "Vacation No", 1, 1)}) and then expand table column "vac"

@AlienSx so you mean after g step? 
It will be not possible inside first Table.Group? 


Ok i got this!!!

Thank you!!

 

@jaryszek yes, after g step, outside first Table.Group. Can't do that inside. Inside first Table.Group you can work with the set of rows (table) of each group only. 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors