Power Query - Net working Days in power query

Networking days is easy to do in Excel however in Power Query it's a bit of a different story. In this video i show you how to create your own custom net working days function in power query.
#m #mquery #excel #powerbi #powerbidesktop #dates #data.
So this video contains a bug for the fix to this video refer to (Applied to Hours)
• Power Query - Calculat...
New GITHUB File (Based on Working hours video and file)
github.com/jbo...
Links
Create a Date List
learn.microsof...
Remove Weekends from Date List
learn.microsof...
Remove Holidays from Date List
learn.microsof...
Count the rows in List
learn.microsof...
Documentation on Duration
learn.microsof...
My site
www.basensei.com

[@damilolalasode4381]

This is a very insightful video. But I noticed a mistake in the List.Select( ) section. To get the correct count, It should be

[@leehughes3075]

this is correct, thank you both, was wondering why it was giving me too many days initially.. tried this and cross referenced with AI and yeah you right.

[@danieviljoen5911]

Hi, how do we get the list of the networking days?

[@DillibabuNagarajan]

Thanks for providing this training program. one small correction is required when the start date is in the month of Feb and the End Date is above 1st March. Maybe some more coding needs to be included. I have tried with the Start date of 1st Feb 2023 to 1st March 2023. the result shows 24

[@basensei8699]

ooo thanks for picking that up - i'm gonna make a follow up on that!

[@tonykim774]

@@basensei8699 This is great since PowerQuery doesn't natively support Networking Days. However, like the Dillibabu mentioned, there seems to be an issue with Feb. For example, I had a start date of 2/8 and an end date of 3/10. That should be 23 business days. However, this function shows 27 business days. If you can provide a solution to this, that would be great!

[@basensei8699]

Thanks Tony! ye i'm actually busy on a video right now to be released this week to address this little bug.

[@teresame]

​@@basensei8699 Hello! was the video to address the little bug (Fed dates) released? I would love to see it but don't it on your channel. Thanks!

[@basensei8699]

@@teresame Yes indeed! here is a link to the corrected file github.com/jbotes/powerbiTutorials/blob/main/PQ_Working%20Days_2_Video.xlsx

[@walterstevens8676]

It doesn't seem to like whatever is my first line after "Let". I get an "Expected EOF Token" on the first word ie DateList in your example, but no matter what I put in that first line I get the same error.. Is there something I'm missing? I just typed in the lines as you did, in a blank query.

[@FireWorksInJuly]

I have multiple date columns I want to see the start and end dates for. How can I make this dynamic? I have different columns and get an error if the column name is not explicitly “start date” or “end date”.

[@kathrynbattenbough4776]

Thanks for this, I don't need the holidays including in mine as it's for a maternity calculator but I just need working days from 1st to last day of each month, I've excluded the holiday element (see code below) but, for example where the start is 01/08/2023 and end is 31/08/2023 it's counting 27 days rather than 23. My date table has the dates formatted in date format with custom column formula, = NetWorkingDays ([Start of Month],[End of Month]). I'm a newbie to writing in mcode so not really sure where I've gone wrong, any help would be really appreciated. Thanks.
(StartDate as date, EndDate as date) =>
let
DateList = List.Dates(StartDate,Number.From(EndDate-StartDate)+1,#duration(1,0,0,0)),
RemoveWeekends = List.Select(DateList, each Date.DayOfWeek(_,Day.Monday)

[@basensei8699]

Hey Kat,
put the following in a new query - It will give you the net working days
------------------------------------------------------------------------
(firstdate as datetime, seconddate as datetime)=>
let
DateList = List.Generate(()=>
firstdate,
each _

[@basensei8699]

To see how the query works applied to working hours (the logic of mquery)
ua-cam.com/video/61daAc8ML1o/v-deo.html
the query i sent you above i removed the time element and just focused on the days ....
hope it makes sense.

[@kathrynbattenbough4776]

@@basensei8699 thanks for replying, I'll check it out!

[@RH-pz1du]

@@basensei8699 Thank you very much for this!!!

[@alirezahossini463]

Pretty cool

[@sarahverelst8131]

It's not addressed in the video that you get a different value using the Power Query formula versus the NetWorkingDays formula of excel.