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

    • Vijay_A_Verma's avatar
      Vijay_A_Verma
      Most Valuable Professional
      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])
   )
)
      • DonPepe's avatar
        DonPepe
        Helper II
        4 years ago

        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 !