cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fsim
Resolver II
Resolver II

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

 

1 ACCEPTED SOLUTION
fsim
Resolver II
Resolver II

@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

userIdprojectidlistdatehours
01alpha07/03/20228
01alpha08/03/20228
01alpha09/03/20228
01alpha14/03/20228
01alpha15/03/20228
01alpha16/03/20228
01alpha21/03/20228
01alpha22/03/20228
01alpha23/03/20228
01alpha28/03/20228
01alpha29/03/20228
01alpha30/03/20228
01alpha04/04/20228
01alpha05/04/20228
01alpha06/04/20228

 

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.

 

 

 

View solution in original post

6 REPLIES 6
fsim
Resolver II
Resolver II

@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

userIdprojectidlistdatehours
01alpha07/03/20228
01alpha08/03/20228
01alpha09/03/20228
01alpha14/03/20228
01alpha15/03/20228
01alpha16/03/20228
01alpha21/03/20228
01alpha22/03/20228
01alpha23/03/20228
01alpha28/03/20228
01alpha29/03/20228
01alpha30/03/20228
01alpha04/04/20228
01alpha05/04/20228
01alpha06/04/20228

 

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
Resolver II
Resolver II

@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

 

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?

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,

 

it was mon tue wend, in my example because of the 3 dates. 

Thejeswar
Super User
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,

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.