Power Query Duration between Dates excluding Weekends Wont Load

[Austen229022]

I am using a database to import rows of details into my dashboard, with a calculation of working days between the Received and the Issued dates. There are around 120k rows, and only have Case ID, Received Date, Issued Date and 2 other columns in the table.

A Calendar list has been created taking out all the working days so it is just weekends and public holidays.

From this, I have created a query as follows:

(StartDate as date, EndDate as date, HolidayList as list) as number=>
let
    DateList=List.Dates(StartDate,Number.From(EndDate-StartDate)+1,#duration(1,0,0,0)),
    //create series of Dates
    RemoveHolidays=List.RemoveItems(DateList, HolidayList),
    //remove holidays
    Countdays=List.Count(RemoveHolidays)
    //count days
in
  Countdays

When I then add this into my table as below, it takes literal hours to load and I have to close the program through the Task Manager.

Can anyone help with this issue so that it will actually load? 

[v-priyankata]

Hi @Austen229022 
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[v-priyankata]

Hi @Austen229022 
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[maruthisp]

Hi Austen229022,
Instead of doing the step in Power Query, can you try doing with DAX?
Step-1: Create a Date table with excluding weekends and holidays
Step-2: Use the above Date table to join with your table

Best regards,
Maruthi

[Austen229022]

How can you do this with DAX?

[v-priyankata · Accepted solution]

Hi @Austen229022,

I have reproduced your scenario in Power BI as per your requirements. Please find attached.pbix file for your reference. 

If this response helps, consider marking it as “Accept as solution” and giving a “kudos” to assist other community members.
Thank you.

[v-priyankata]

Hi @Austen229022 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you

[Ashish_Excel]

Hi,

It may be better to do this in DAX as a calculated column formula.  Share some data to work with and show the expected result.  Also share the list of public holidays.