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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
fsim
Responsive Resident
Responsive Resident

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
Responsive Resident
Responsive Resident

@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
Responsive Resident
Responsive Resident

@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
Responsive Resident
Responsive Resident

@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
Resident Rockstar
Resident Rockstar

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
Resident Rockstar
Resident Rockstar

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
Responsive Resident
Responsive Resident

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

Thejeswar
Resident Rockstar
Resident Rockstar

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors