How do I use Power Query and/or Power Pivot to replace the standard Excel networkdays function?
Specifically trying to deal with the optional Holidays argument. I am using Power Query to look at daily excel files in a network folder that contain approximately 250,000 rows. Each day, the data file gains 5,000 to 10,000 rows and loses the same based on aging. Each record contains CreatedDate and CompletedDate.
In the past we've copied and pasted the daily file into an Excel table and let an extra column calculate networkdays that referenced a hard coded 'HOLIDAYS' worksheet. My analysis requires grouping by the days such as 0-5, 6-10, etc. so I was hoping avoid bringing all of the detail data into Excel and just load to the Data model so I can calc networkdays in the data model as well.
Thanks in advance for your help.
Regards,
Paul W
Hi Paul,
There is no NETWORKDAYS function in Power Query or Power Pivot, as you already know.
In Power Query you have to write a custom function, as covered here:
https://www.powerquery.training/portfolio/networkdays/
You’d need to modify this custom function to allow for your holiday dates as well as weekends.
Or in Power Pivot you use a separate unrelated ‘Dates’ table that holds the information about whether your date is a workday (1) or not a workday (0). In the link below they use SWITCH to generate this automatically but this only allows for weekends so you’d be best to maintain this as a linked table which also contains whether the date is a holiday or not:
https://www.powerpivotpro.com/2012/11/networkdays-equivalent-in-powerpivot/
If you’re planning on creating a model then I’d probably do this calculation in PowerPivot as opposed to Power Query, but if you don’t need to use Power Pivot then go with the Power Query solution.
Mynda
HI Mynda,
can you show me how to modify the custom function to allow for holiday dates as well as the weekends?
Thanks,
Lisa
Hi Lisa,
The Power Query method is covered here: https://www.powerquery.training/portfolio/networkdays/
Then for the holiday dates, create another query for your holiday dates table. Then join the networkdays table and the holiday table using the date columns. Then use the filter to remove the holidays from the joined table. Count the remaining days.
Mynda
