Forum Discussion
Responsive Residentlist.generate from a list
hi !
I have a list of dates , a repeating factor and an end date. something like this:
{"07/03/2022", "08/03/2022";"09/03/2022"}, every week, until "31/07/2022". In other words, there is a reccuring task every mon, tue, wend, starting the 7th of march until the end of July.
I want the full list of all the dates
At first I thought about list.date with List.Dates([start_date],(Duration.Days([repeat_end_date]-[start_date])+1)/7,#duration(7,0,0,0)) but start date should be my list and not a column in my table.
So I guess, I should use some list.generate, but I don't see what I'm suppose to put in.
List.Generate(
() => [ListDate],
each _ < [repeat_end_date],
_ = Date.AddDays( _, 7)
)
but obviously I don't understand how it works 😕
any idea ?
most of the sample I found on the web are using list.generate with basic calculation and no list 😕
thanks in advance
here is how I managed to do it.
First of all, let me explain the full process. (I should have started with this)
Users are asked to encode their planning workload for the coming year. They fill in a form with those 7 fields.
userId, projectId, starting_date, ending_date, hours, repeateveryweek, repeat_end_date
for example, user 01, working on project "alpha" plans to work 8h/day from "07/03/2022" to "09/03/2022" and repeat this workload every 1 week until the end of July.
01,"alpha","07/03/2022","09/03/2022",8,1,"31/07/2022".
what I want, at the end is something like this until end of julyuserId projectid listdate hours 01 alpha 07/03/2022 8 01 alpha 08/03/2022 8 01 alpha 09/03/2022 8 01 alpha 14/03/2022 8 01 alpha 15/03/2022 8 01 alpha 16/03/2022 8 01 alpha 21/03/2022 8 01 alpha 22/03/2022 8 01 alpha 23/03/2022 8 01 alpha 28/03/2022 8 01 alpha 29/03/2022 8 01 alpha 30/03/2022 8 01 alpha 04/04/2022 8 01 alpha 05/04/2022 8 01 alpha 06/04/2022 8 what I did:
I first converted startdate and endate to a list of dates using List.Dates([start_date],Duration.Days([end_date]-[start_date])+1, #duration(1,0,0,0)))
then I splitted the list in new rows with = Table.ExpandListColumn
Last I repeated this process but this time using the repeat_end_date instead of end_date
= List.Dates([ListDate],(Duration.Days([repeat_end_date]-[ListDate])+1)/7*repeateveryweek, #duration(7*repeateveryweek,0,0,0))
And split that list again in new rows.
thanks for all your inputs. it helped me figuring out how to solve this.
6 Replies
- fsim
here is how I managed to do it.
First of all, let me explain the full process. (I should have started with this)
Users are asked to encode their planning workload for the coming year. They fill in a form with those 7 fields.
userId, projectId, starting_date, ending_date, hours, repeateveryweek, repeat_end_date
for example, user 01, working on project "alpha" plans to work 8h/day from "07/03/2022" to "09/03/2022" and repeat this workload every 1 week until the end of July.
01,"alpha","07/03/2022","09/03/2022",8,1,"31/07/2022".
what I want, at the end is something like this until end of julyuserId projectid listdate hours 01 alpha 07/03/2022 8 01 alpha 08/03/2022 8 01 alpha 09/03/2022 8 01 alpha 14/03/2022 8 01 alpha 15/03/2022 8 01 alpha 16/03/2022 8 01 alpha 21/03/2022 8 01 alpha 22/03/2022 8 01 alpha 23/03/2022 8 01 alpha 28/03/2022 8 01 alpha 29/03/2022 8 01 alpha 30/03/2022 8 01 alpha 04/04/2022 8 01 alpha 05/04/2022 8 01 alpha 06/04/2022 8 what I did:
I first converted startdate and endate to a list of dates using List.Dates([start_date],Duration.Days([end_date]-[start_date])+1, #duration(1,0,0,0)))
then I splitted the list in new rows with = Table.ExpandListColumn
Last I repeated this process but this time using the repeat_end_date instead of end_date
= List.Dates([ListDate],(Duration.Days([repeat_end_date]-[ListDate])+1)/7*repeateveryweek, #duration(7*repeateveryweek,0,0,0))
And split that list again in new rows.
thanks for all your inputs. it helped me figuring out how to solve this.
- Thejeswar
Super User
Hi fsim ,
I did not use List.Generate() to get this.
But was able to get it using a different approach
In case, if you find it usefule, the below is the M Query
let Source = List.Dates(#date(2022, 03, 07),Duration.Days((#date(2022, 08, 01)-#date(2022, 03, 07))) , #duration(1, 0, 0, 0)), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Inserted Day of Week" = Table.AddColumn(#"Converted to Table", "Day of Week", each Date.DayOfWeek([Column1]), Int64.Type), #"Filtered Rows" = Table.SelectRows(#"Inserted Day of Week", each ([Day of Week] = 0 or [Day of Week] = 1 or [Day of Week] = 2)) in #"Filtered Rows"Regards,
- Thejeswar
Hi fsim ,
I did not filter on the dates.
Since you already said that the task is recurring on every Monday, Tuesday and Wednesday, I am generating Day of week column to get 0, 1 and 2 respectively for these days. i.e. 0th day in a week is Monday, 1st day is tuesday and 2nd day is wednesday.
Now I filter on this column restricting the day which have day of week as 0, 1 and 2.
So this restricts all Mondays, Tuesdays and Wednesdays.
Regards,
- fsim
it was mon tue wend, in my example because of the 3 dates.
- Thejeswar
Hi,
How do you get the list of dates to be filtered.. ? I mean how do you get the details on the dynamic date list?
