In financial analysis and reporting, it’s crucial to account for non-banking days when processing dates. This Power Query function helps determine the next available banking day when given a date input. It skips weekends and holidays, ensuring that the returned date is a valid working day.

How the Function Works

  1. Check for Non-Banking Days

    • The function IsNonBankingDay determines if a given date falls on a weekend (Saturday or Sunday) or is a holiday (from a predefined list).

  2. Find the Next Banking Day

    • If the input date is a non-banking day, the function GetNextBankingDay recursively moves to the next day until it finds a valid banking day.

  3. Return the Result

    • If the input date is already a banking day, it is returned unchanged. Otherwise, the function finds and returns the next available banking day.

Power Query Code

(inputDate as date) =>
let
    // Helper function to check if a date is a non-banking day
    IsNonBankingDay = (dateToCheck as date) => 
        let
            IsHoliday = List.Contains(Holidays, dateToCheck),
            IsWeekend = Date.DayOfWeek(dateToCheck, Day.Monday) >= 5
        in 
            IsHoliday or IsWeekend,

    // Recursive function to find next banking day
    GetNextBankingDay = (dateToCheck as date) =>
        let
            NextDay = Date.AddDays(dateToCheck, 1)
        in
            if IsNonBankingDay(NextDay) then
                @GetNextBankingDay(NextDay)
            else
                NextDay,

    // Main logic
    outputDate = if IsNonBankingDay(inputDate) 
                     then GetNextBankingDay(inputDate) 
                     else inputDate
in
    outputDate

Note: it is important that you have a query called Holidays which is a list of all holidays for the date range that covers your inputDate.

Recursive Function Call

In Power Query M language, the @ symbol is used for recursive function calls. It’s a self-reference operator that allows a function to call itself.

When you use @GetNextBankingDay, you’re explicitly telling Power Query that you want to call the same function recursively. It’s similar to how you might use this in other programming languages to refer to the current instance.

Here’s a breakdown:

GetNextBankingDay = (dateToCheck as date) =>
    let
        NextDay = Date.AddDays(dateToCheck, 1)
    in
        if IsNonBankingDay(NextDay) then
            @GetNextBankingDay(NextDay)  // Recursive call to the same function
            //^ The @ here means "this same function"
        else
            NextDay

Without the @, Power Query wouldn’t know you’re trying to make a recursive call to the same function you’re currently defining. The @ symbol makes this explicit and helps avoid any ambiguity, especially in cases where you might have functions with the same name in different scopes.

It’s worth noting that while recursion is powerful, you should be careful with it as Power Query has limitations on recursion depth to prevent infinite loops.

Example Usage

If Holidays = {#date(2024, 1, 1), #date(2024, 12, 25)} and you input:

  • #date(2024, 1, 1) (New Year's Day - a holiday) → Returns #date(2024, 1, 2)
  • #date(2024, 6, 15) (Saturday) → Returns #date(2024, 6, 17)
  • #date(2024, 6, 18) (Tuesday) → Returns #date(2024, 6, 18) (already a banking day)

Why Use This Function?

  • Automates Banking Date Calculations – No need to manually adjust dates for non-working days.
  • Ensures Accuracy in Financial Reports – Prevents transactions from being scheduled on closed banking days.
  • Handles Custom Holiday Lists – Can be adapted to specific regional or company-defined holidays.

This function is useful in scenarios such as payment processing, interest calculations, or any business process that relies on valid banking days.