Forum Discussion
Power Query Duration between Dates excluding Weekends Wont Load
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?
Hi Austen229022,
I have reproduced your scenario in Power BI as per your requirements. Please find attached
.pbixfile 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.
7 Replies
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.
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,
MaruthiHow can you do this with DAX?
Hi Austen229022,
I have reproduced your scenario in Power BI as per your requirements. Please find attached
.pbixfile 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.
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.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.
