Custom Function: Networkdays load rows very slowly

[LarsEva]

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.

[Vijay_A_Verma · Accepted solution]

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)))))

[LarsEva]

This worked out really well. Thank you for the help!