Forum Discussion

DonPepe's avatar
DonPepe
Helper II
4 years ago
Solved

Convert List in Function

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_FREQUENCYDAY_IN_WEEKService validity start dateService validity end date
SDF12321234212-06-2231-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 

Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    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])
   )
)

7 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    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)
  • KT_Bsmart2gethe's avatar
    KT_Bsmart2gethe
    Impactful Individual
    4 years ago

    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's avatar
    DonPepe
    Helper II
    4 years ago

    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's avatar
      Vijay_A_Verma
      Most Valuable Professional
      4 years ago

      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's avatar
        DonPepe
        Helper II
        4 years ago

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