Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
LarsEva
New Member

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.

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

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

 

View solution in original post

2 REPLIES 2
Vijay_A_Verma
Super User
Super User

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!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors