Today I’m going to share my custom NETWORKDAYS function for Power Query with you that uses the same syntax than its Excel-equivalent.
NETWORKDAYS function
This function’s first 3 parameters work just like the Excel function and there is a 4th parameter that allows adjusting the day on which the week shall start:
- Start as date
- End as date
- optional holidays as list of dates
- optional number to change the start of the week from Monday (default: 1) to any other day (2 would mean that the week starts on Tuesday instead)
The function comes with a UI that lets you first choose a table containing the holidays and then choose the column with the holiday dates.
But you can also type in the list of holidays in manually. Therefore leave the optional parameter blank if you use it through the UI and edit the formula afterwards like so:
fnNETWORKDAYS ( StartDate, EndDate, {#date(2020, 1, 1) {#date(2020,12,25)} ), adding all necessary dates into the 3rd parameters list.
The Code
Twists
If your holidays don’t sit in a dedicated table but in separate column of a calendar table like so:
I’d recommend to reference that table, filter only holidays and then reference it’s data column like mentioned before.
Enjoy & stay queryious 😉
The post Date.Networkdays function for Power Query and Power BI appeared first on The BIccountant.
Read the Full Article here: >The BIccountant