Working Days Between 2 Tables From Dates Table

Power Query
1

Hi All,

I have table with multiple date columns and a date table.

I need to work out the work days between each column from the dates table. How would I go about this?

I’m looking for a Power Query solution as opposed to DAX. Ideally I would like to have a function to be called within the table to calculate this.

Expected Result:

image
image2123×54 6.22 KB

Sample.pbix (41.2 KB)

Attaching a sample file.

Thanks in Advance

2

I used Data Mentor for some assitance and came up with this function.

Whilst the function works, when invoking it to add multiple columns it takes a performance hit. Are there recommendations to optimise?

/**
* Function to calculate total working days between start date and end date using a work day column from the date table.
*
* @param FactTable as table: The fact table containing the start and end dates.
* @param DateTable as table: The date table containing the date and work day information.
* @param StartDateColumn as text: The column name in the fact table representing the start date.
* @param EndDateColumn as text: The column name in the fact table representing the end date.
* @param WorkDayColumn as text: The column name in the date table representing if it's a work day.
* @return table: Fact table with an additional column representing the total working days.
* @raises error: If any input parameter is invalid.
*/
let
  CalculateWorkingDays = (
    FactTable as table,
    DateTable as table,
    StartDateColumn as text,
    EndDateColumn as text,
    WorkDayColumn as text,
    ColumnName as text
  ) as table =>
    let

      // Input Validation
      _validateInputs =
        if (Table.IsEmpty(FactTable) or Table.IsEmpty(DateTable)) then
          error "FactTable and DateTable cannot be empty"
        else if not (Table.HasColumns(FactTable, {StartDateColumn, EndDateColumn})) then
          error Text.Format(
            "FactTable must contain columns named '#{0}' and '#{1}'",
            {StartDateColumn, EndDateColumn}
          )
        else if not Table.HasColumns(DateTable, {WorkDayColumn}) then
          error Text.Format("DateTable must contain a column named '#{0}'", {WorkDayColumn})
        else
          FactTable,
      // Add a custom column to calculate total working days
      AddedWorkingDays = Table.AddColumn(
        _validateInputs,
        ColumnName,
        each
          let
            startDate = Record.Field(_, StartDateColumn),
            endDate = Record.Field(_, EndDateColumn),
            // Filter DateTable for the range and only work days
            Result =
              if endDate = null then
                null
              else
                let
                  FilteredDateTable = Table.SelectRows(
                    DateTable,
                    each
                      let
                        currentDate = Record.Field(_, "Date"),
                        isWorkDay   = Record.Field(_, WorkDayColumn)
                      in
                        Date.From(startDate)
                          <= currentDate and currentDate
                          <= Date.From(endDate) and isWorkDay
                  ),
                  // Count the rows which represents the working days in the filtered range
                  WorkingDaysCount = Table.RowCount(FilteredDateTable)
                in
                  WorkingDaysCount
          in
            Result,
        Int64.Type
      )
    in
      AddedWorkingDays
in
  CalculateWorkingDays
3

Hi @adsa,

In your Expected Result, AssessedToFinalised Days shows 1, but 4-4-2021 isn’t a working day. Therefore, in the solution below, it returns 0. Adjust if necessary. Give this a go:

let
    listWorkdays = List.Buffer( Table.SelectRows( #"Date Table", each [IsWeekDay] =1)[Date] ),
    fxWorkdayCount = (workdayList as list, optional startDate as date, optional endDate as date) as number => 
        List.Count( List.Select( workdayList, (x)=> x >= startDate and x <= endDate )),
    Source = Summary,
    ChType = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Created", type date}, {"Submitted", type date}, {"Assessed", type date}, {"Finalised", type date}}),
    AddFields = Table.AddColumn(ChType, "NewFields", each [
        CreatedToSubmitted Days = fxWorkdayCount( listWorkdays, [Created], [Submitted]), 
        SubmittedToAssessed Days = fxWorkdayCount( listWorkdays, [Submitted], [Assessed]), 
        AssessedToFinalised Days = fxWorkdayCount( listWorkdays, [Assessed], [Finalised])
        ], type [
            CreatedToSubmitted Days = Int64.Type, 
            SubmittedToAssessed Days = Int64.Type, 
            AssessedToFinalised Days = Int64.Type
        ]
    ),
    ExpandFields = Table.ExpandRecordColumn(AddFields, "NewFields", 
        {"CreatedToSubmitted Days", "SubmittedToAssessed Days", "AssessedToFinalised Days"}
    )
in
    ExpandFields

Let me know how this compares performace wise.
I hope this is helpful

1 Like
4

Hi @Melissa,

That works flawlessly and is a significant boost in performance!

image

Just for my understanding, I created function (See below):

  • I want to return a null if the end date is a null instead of 0. I added an if statement. In your view is this better or replace values after the function has been invoked?
    *Can the list be buffered within the function or does it have to be within the query?
let
   fxWorkdayCount = (workdayList as list, optional startDate as date, optional endDate as date) as nullable number =>
      if endDate is null then
         null
      else
         List.Count(List.Select(workdayList, (x) => x >= startDate and x <= endDate))
in
   fxWorkdayCount

Thanks

5

Thanks for reporting back @adsa :+1:

No, stick with the if-statement. Two reasons, its more accurate as a 0 could also be a valid outcome when two dates are supplied and I expect it will be faster.

I expect that to come at a cost, because that will be executed each time the function is invoked…

6

Thanks @Melissa