Convert List in Function

[DonPepe]

Hi, 

My goal is to get all the working date for a service from a given Start date and a given End date knowing that I know only the day of week and the service validity. 

Parameters :  

• Start date = 21/6/2022
• End date = 15/7/2022

Data :

Service ID	Service_FREQUENCY	DAY_IN_WEEK	Service validity start date	Service validity end date
SDF1232	1234	2	12-06-22	31-12-25

So the service SDF1232 work from Monday (frequency 1) to Thursday (Frequency 4) and here I have the record of Thuesday (frequency 2 = day in week 2) his validity is from 12-06-22 to 31-12-25.

Basicaly, here, I want all the Thuesday between Start date 21/6/2022 and end date 15/7/2022 since the two parameters are included into the service validity date. 

The think to choose the range between the parameters or the service validity is ok. 

Where I strugle is how can I get the list of each Thursday. 

I tried like this but I have an error that Power Query cannot convers list type to function type and I dont know how to resolve it.

let
   Source = RawFlexiView, 
   ListDayWeek = Table.AddColumn(
      Source, 
      "Date", 
      List.Select(
         {Number.From(Date.FromText(StartDate)) .. Number.From(Date.FromText(EndDate))}, 
         each Date.DayOfWeek(
            _, 
            Lines.ToText(
               List.First(
                  List.Range(
                     {
                        Day.Sunday, 
                        Day.Monday, 
                        Day.Tuesday, 
                        Day.Wednesday, 
                        Day.Thursday, 
                        Day.Friday, 
                        Day.Saturday
                     }, 
                     Number.From([PLAOSP Start Day In Week]), 
                     1
                  )
               )
            )
         )
            = 0
      )
   )
in
   ListDayWeek

If I dont use the right strategy tel me also, I am quite new on Power Query.  

 Thanks a lot for your help,

Don 

[KT_Bsmart2gethe]

Hi @DonPepe ,

Highlighted in blue is the day of the week.

List.Transform(List.Select({Number.From(StartDate)..Number.From(EndDate)}, each Date.DayOfWeek(Date.From(_))+1=2), each Date.From(_))

Regards

KT

[DonPepe]

Hi @Vijay_A_Verma,

Thanks a lot for your answer, it's ok for this example. I just include it in a new column. 

Table.AddColumn(
   Source, 
   "DateList", 
   each List.Select(
      List.Dates(
         Date.FromText(StartDate), 
         Duration.Days(Date.FromText(EndDate) - Date.FromText(StartDate)) + 1, 
         #duration(1, 0, 0, 0)
      ), 
      each Date.DayOfWeek(_) = 2
   )
)

Now I would like to have the same result but for any kind of value in the field "DAY_IN_WEEK" 

I tried this but I have an error :

Table.AddColumn(
   Source, 
   "DateList", 
   each List.Select(
      List.Dates(
         Date.FromText(StartDate), 
         Duration.Days(Date.FromText(EndDate) - Date.FromText(StartDate)) + 1, 
         #duration(1, 0, 0, 0)
      ), 
      each Date.DayOfWeek(_) = Number.From([DAY_IN_WEEK])
   )
)

the error :

"Sorry... We couldn't apply field access to the Date type.
Details :
Value=05-11-23
Key=PLAOSP Start Day In Week"

Thanks a lot,

Don 

[Vijay_A_Verma]

Can you paste your complete code as the error is not coming from this line? The error is referrring to PLAOSP Start Day In Week field which is not being used in this line. 

[DonPepe]

Yeah sorry, PLAOSP Start Day In Week is the name of the field, I simplified it by "DAY_IN_WEEK" in this example.

[Vijay_A_Verma · Accepted solution]

Use this

Table.AddColumn(
   Source, 
   "DateList", 
   each List.Select(
      List.Dates(
         Date.FromText(StartDate), 
         Duration.Days(Date.FromText(EndDate) - Date.FromText(StartDate)) + 1, 
         #duration(1, 0, 0, 0)
      ), 
      (x)=> Date.DayOfWeek(x) = Number.From([DAY_IN_WEEK])
   )
)

[DonPepe]

Woah, so nice, thanks a lot. 

I am not familiar with expression like this

(x)=> Date.DayOfWeek(x)

 I will try to study more on that 🙂 

Have an excellent day ! 

[Vijay_A_Verma]

Use below to generate a list of Tuesday. Fit this in your code

= List.Select(List.Dates([StartDate],Duration.Days([EndDate]-[StartDate])+1,#duration(1,0,0,0)), each Date.DayOfWeek(_)=2)