list.generate from a list

[fsim]

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

[fsim · Accepted solution]

@Thejeswar , 

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 july

userId	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.

[fsim]

@Thejeswar , 

thanks for your comment, 

So you retrieved all the dates, then you filtered on the day you wanted to keep.

It can be usefull, but: I don't know what days are to be kept, it depends on the list of dates. and it's dynamic

[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?

[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 @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,