Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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
Solved! Go to Solution.
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.
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.
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.
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,
User | Count |
---|---|
93 | |
92 | |
85 | |
83 | |
49 |
User | Count |
---|---|
150 | |
142 | |
112 | |
73 | |
55 |