Extract days from event, then count weekdays only - can do in DAX but can't get working in PQ/M

[zhivaninz]

Hi all,

My use case is this:

HR Leave durations come in as events with a start and finish date e.g. 1 Jan - 5 Jan (inclusive). I want to unpack these to day-level granularity so I can assign them to the right periods.

I use this to get my day level granularity:

#"Added Custom" = Table.AddColumn(#"Added Custom1", "DayForLeave", each List.Dates(
    [Leave Start Date],
    Duration.Days( [Leave End Date] - [Leave Start Date] ) +1, 
    #duration(1,0,0,0)
)),
    #"Expanded DayForLeave" = Table.ExpandListColumn(#"Added Custom", "DayForLeave")

I currently prorata the duration of leave (e.g. 3 days) by the number of days for that transaction. For example, if somebody took Friday, Monday, Tuesday off, I'd get 3/5 = 0.6 and so Saturday and Sunday would be allocated 0.6 days each.

I want to ignore weekends. I've got DAX that works, but I want to convert to PQ/M.

IsWeekDay = 
var _weekdayx =
    WEEKDAY(
        DATA_GRAN_LEAVE[DayForLeave],2)

return
IF(
    _weekdayx<=5,
    1,
    0
)
CountWeekDaysforTransaction = 
// how many days during the transaction are weekdays for prorate

var _transaction = 
DATA_GRAN_LEAVE[TransactionIndexKeep]

return
CALCULATE(
    DISTINCTCOUNT(
        DATA_GRAN_LEAVE[DayForLeave]), // focused on how many days
        DATA_GRAN_LEAVE[TransactionIndexKeep]=_transaction, // match the same transaction
        DATA_GRAN_LEAVE[IsWeekDay]=1, // only weekdays
        ALL(
            DATA_GRAN_LEAVE
        )
)

This works and so I can then divide the duration of the leave by this number for weekdays only (e.g. I will get 1 day for Friday, Monday, and Tuesday in my example above, and 0 for Saturday and Sunday).

I want this in PQ but I'm hitting a brick wall. Either my code is wrong or it's incredibly inefficient. I've used a DAXian syntax so would love any advice.

#"Added Custom5" = Table.AddColumn(#"Changed Type3", "WeekdaysForDuration", each let
_transactionindexvar = [TransactionIndexKeep],
_dayweekvar = [LeaveDayOfWeek],
_numberofweekdaysvar =
Table.RowCount(
    Table.SelectRows(
         #"Changed Type3",
         each 
         _transactionindexvar=[TransactionIndexKeep]
         and _dayweekvar<5
     )
 )
 in _numberofweekdaysvar),

Any advice would be gratefully accepted. The column runs, but never resolves.

[ronrsnfld]

Kindly provide a data sample (as text that can be copy/pasted) and a picture of your desired output from that data. Use made-up data that is representative if actual data is confidential.

[zhivaninz]

Something like this, with the last two columns what I want in PQ.

TransactionIndex	Duration	DayForLeave	IsWeekday	WeekdaysForLeave	ProRata
1	3	11-Aug-23	1	3	1
1	3	12-Aug-23	0	3	0
1	3	13-Aug-23	0	3	0
1	3	14-Aug-23	1	3	1
1	3	15-Aug-23	1	3	1
2	5	14-Aug-23	1	5	1
2	5	15-Aug-23	1	5	1
2	5	16-Aug-23	1	5	1
2	5	17-Aug-23	1	5	1
2	5	18-Aug-23	1	5	1
3	1	11-Aug-23	1	1	1
3	1	12-Aug-23	0	1	0
3	1	12-Aug-23	0	1	0

[zhivaninz]

Duration is a fixed value but MAY NOT be the same as the number of weekdays in the transaction

[ronrsnfld]

I'm not sure why you need the next to last column.

To obtain the "Pro Rata" column, you only need the "DayForLeave" Column:

    #"Add Pro Rata" = Table.AddColumn(#"Changed Type", "Pro Rata", each 
        if Date.DayOfWeek([DayForLeave])=Day.Saturday 
            or Date.DayOfWeek([DayForLeave]) = Day.Sunday 
        then 0 else 1, Int64.Type)

[zhivaninz]

Pro rata divides total duration of leave by the number of weekdays - in this case they are all 1 but this is not always the case.

[ronrsnfld]

I don't think that is what you are showing in your example.

What am I missing?

Date	Total Duration of Leave	Weekdays for Leave	dur / Wd for leave	your pro rata column
11-Aug	3	3	1	1
12-Aug	3	3	1	0

[zhivaninz]

I'm explaining this poorly.

The initial data is in the form of a Duration from X to Y for each LeaveTransaction. This includes a value for the number of days of leave taken during this Duration (let's call this LeaveCost). LeaveCost and Duration are not the same as there may be weekends or public holidays.

I then expand this to get one row per day from X to Y inclusive. Currently, I divide LeaveCost by the number of days, but this means weekends are getting a pro-rata amount of LeaveCost.

I want to divide LeaveCost by the number of non-weekend days during Duration. So, I need a way to calculate this number.

[ronrsnfld]

It sounds to me that 

• The data you presented was neither representative of your real data and also included extra stuff that is not in your original data
  •  no durations
  • an exanded date column including all dates
  • no instances where "Weekdays For Leave" and "Pro Rata" are not the same.
• And I wonder if your final picture represents what you really need.
  • Do you really need a column indicating whether a day is a weekday or not?
  • Do you really need a column with every date in it and that other information, or is that there because that is what you were doing in DAX?
  • If all you need, for example,  "Duration", "Count of Weekday Days" and "Pro Rata" for each transaction (along with the (Transaction Number and start and end dates), then there is no need for the other columns and the "M" code would be more straightforward.

Your problem seems pretty simple to solve in "M", but, again, a proper, representative data sample as I previously requested, along with a screenshot of what you really need from that result, would be necessary for clarification.

I will be out of town for a few days, so please try to work something up that is more representative of your starting point and eventual goal.

[zhivaninz]

Hi Ron

I've been slack in my terminology, so let me clarify:

Duration in this post means the length of the leave (from day X to day Y)

LeaveCost is how many days of leave are consumed during that Duration

There are many columns I haven't included as they're not relevant. I've already managed to expand single full-duration rows into one row per day. Each of these source rows has its own index.

The next step in the logic is to identify whether a row is a weekend or not (IsWeekDay). And then, for each unique transaction (original index), count the number of weekdays for that transaction - and divide the LeaveCost by that number.

I've tried just using the original Duration of Leave/LeaveCost by itself where a row is a weekday, and leaving weekends blank (e.g. in the above case, for each transaction, if weekday =1, [Duration][Duration]

As I mentioned in my first post, I have DAX code that works, so what I need is the equivalent in PQ.

Cheers!

[ronrsnfld]

I still don't understand why you need all those extra columns, but, assuming that you do, the Power Query "M" method to add those last two columns could be as follows:

let

//Change Source line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Leaves"]}[Content],

    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"TransactionIndex", Int64.Type}, {"Duration", Int64.Type}, {"DayForLeave", type date}, {"IsWeekday", Int64.Type}}),

//Add Weekdays for Leave Column and Leave ProRata Columns

//Group by TransactionIndex
// do the calculations
// then expand the "table" column
    #"Group by TransactionIndex" = Table.Group(#"Changed Type",{"TransactionIndex"},{
        {"All", each (_), type table[TransactionIndex=Int64.Type, Duration=Int64.Type, DayForLeave=date, IsWeekday=Int64.Type]},
         {"Weekdays for Leave", each List.Sum([IsWeekday]),Int64.Type},
        {"LeaveProRata", each List.Sum([IsWeekday]) / [Duration]{0}, Int64.Type}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Group by TransactionIndex", "All", {"Duration", "DayForLeave", "IsWeekday"}),

//If day of week = weekend then ProRata = 0
    #"Zero Weekends" = Table.ReplaceValue(#"Expanded All", 
        each [LeaveProRata],
        each if [IsWeekday]=0 then 0 else [LeaveProRata],
        Replacer.ReplaceValue,
        {"LeaveProRata"})
in
    #"Zero Weekends"

Original Data

Results