Forum Discussion
Custom Function: Networkdays load rows very slowly
Hi, i'm new to using power query and i've been trying to calculate the networkdays of 2 columns in my table. I am using a custom function i found on the web (which i think everyone is using)
That uses 3 variables
(StartDate as date, EndDate as date, HolidayList as List) => etc etc
It does work, but my problem is; it loads rows so slowly with an estimate of 1 row per 1.5 sec. I have 14k rows that im trying to calculate so this might take more than 5 hours if i let it load like this. Does anyone know any workaround?
All my datasources are coming from an excel sheet from a sharepoint server set up by my organization.
I don't want to do the calculation from my the excel file as it might cause a slowdown in the document.
Use following formula in a custom column to calculate Networkdays. You will need to replace Start and End
List.Count(List.Select(List.Dates([Start], Duration.Days([End]-[Start])+1, #duration(1,0,0,0)), each Date.DayOfWeek(_,1)<5))If you want a very fast way, then insert this step. Replace #"Changed Type" with your previous step and of course, Start and End.
= Table.FromRecords(List.Transform(Table.ToRecords(#"Changed Type"), (x)=> Record.AddField(x, "Networkdays", List.Count(List.Select(List.Dates(x[Start], Duration.Days(x[End]-x[Start])+1, #duration(1,0,0,0)), each Date.DayOfWeek(_,1)<5)))))
2 Replies
Use following formula in a custom column to calculate Networkdays. You will need to replace Start and End
List.Count(List.Select(List.Dates([Start], Duration.Days([End]-[Start])+1, #duration(1,0,0,0)), each Date.DayOfWeek(_,1)<5))If you want a very fast way, then insert this step. Replace #"Changed Type" with your previous step and of course, Start and End.
= Table.FromRecords(List.Transform(Table.ToRecords(#"Changed Type"), (x)=> Record.AddField(x, "Networkdays", List.Count(List.Select(List.Dates(x[Start], Duration.Days(x[End]-x[Start])+1, #duration(1,0,0,0)), each Date.DayOfWeek(_,1)<5)))))This worked out really well. Thank you for the help!
