Forum Discussion

ghaines's avatar
ghaines
Icon for Resolver I rankResolver I
3 years ago

Need help determining last workday before a particular day in Power Query

I'm working to complete a date table where there is a "IsWorkday" column that is false for weekends and holidays.  I'm trying to find the workday that a sale should be counted for (They are counted for the previous day i.e. On Monday, the sales report for Friday is generated that includes weekend online sales)

 

I am trying to generate a list from the workdays previous to the current date, but running into issues.  Here is my attempt:

 

 

= Table.AddColumn(#"Renamed Columns", "CountedOn",  let _ = [AccDate] in each 
    List.Max(
        Table.Column(
            Table.SelectRows(
                #"Renamed Columns", each [IsWorkday] = true and [AccDate] <= _
            )
            , "AccDate"
        )
    )
)

 

 

Expression.Error: We cannot apply operator < to types Record and Date.
Details:
Operator=<
Left=
AccDate=1/07/2021
IsWorkday=TRUE
Month=Jul 2021
MonthKey=202201
Right=1/07/2021

 

If I modify 
[AccDate] <= _
to 
[AccDate] <= _[AccDate]
then it returns the latest date in my entire date table on every row.   Something seems to be wrong with my let expression?  But I can't work it out.

Need Help

1 Reply

  • ghaines's avatar
    ghaines
    Icon for Resolver I rankResolver I
    3 years ago

    I have found a solution by not applying the date restriction or finding the max, instead returning the list of all workdays, and then adding a column using:

     

    = Table.AddColumn(Custom2, "PreviousWorkday", each let __ = [AccDate] in List.Max(List.Select([List], each _ <= __)))

     

    I'm not especially happy with it, so please help me with the question if possible.