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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

NETWORKDAYS in PowerQuery Editor

Hi,

Can someone help me how to caluculate Difference between two DateTime columns without public holidays and weekend?

I have startdate and enddate in one table, Holidays dates list in other table.

It would be a great help.

Thank you. 

2 ACCEPTED SOLUTIONS

Hi @Anonymous ,

this function should work:

 

 

(StartDate as date, EndDate as date, optional Holidays as list) =>
let
    ListOfDates = List.Dates(StartDate,Number.From(EndDate-StartDate),#duration(1,0,0,0)),
    DeleteHolidays = if Holidays = null then ListOfDates else List.Difference(ListOfDates, Holidays),
    DeleteWeekends = List.Select(DeleteHolidays, each Date.DayOfWeek(_,1) < 5 ),
    CountDays = List.Count(DeleteWeekends)
in
    CountDays

 

 

reference the column with the holiday dates in its 3rd parameter

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Mariusz
Community Champion
Community Champion

Hi @Anonymous 

 

Please see the attached file with a solution.

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

View solution in original post

9 REPLIES 9
Syndicate_Admin
Administrator
Administrator

Hi, what if an weekend day is actually a working day by some reasons? Is there any method to calculate back that weekend day as working day? Like how to create another list for special working day which is actually in some of weekend day?

 

Mariusz
Community Champion
Community Champion

Hi @Anonymous 

 

Please see the attached file with a solution.

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

Anonymous
Not applicable

Thank you for a this solution. 

Greg_Deckler
Community Champion
Community Champion

@ImkeF might have a solution for Power Query. I wrote one for DAX. https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362#M109



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Thank you for your quick response.

I would like to have it in Power Query, i want to use that column in further caluculations. Is there any function where i can enter my startdate, enddate columns and compare and exclude the weekend and holidays?

I'm not the Power Query guru, which is why I tagged @ImkeF . I will say that you could implement the DAX as a column instead of a measure.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Hi @Anonymous ,

this function should work:

 

 

(StartDate as date, EndDate as date, optional Holidays as list) =>
let
    ListOfDates = List.Dates(StartDate,Number.From(EndDate-StartDate),#duration(1,0,0,0)),
    DeleteHolidays = if Holidays = null then ListOfDates else List.Difference(ListOfDates, Holidays),
    DeleteWeekends = List.Select(DeleteHolidays, each Date.DayOfWeek(_,1) < 5 ),
    CountDays = List.Count(DeleteWeekends)
in
    CountDays

 

 

reference the column with the holiday dates in its 3rd parameter

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

Thank you for a quik and easy solution.

Hi @Anonymous 

 

Thanks for accepting my solution.

Actually I think @ImkeF  solution is probably better, therefore I hope you don't mind that I marked her post as a solution as well, for the benefit of other Community members.

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

Top Solution Authors