You mention working days and that the file contains projects without an end date. However, the file seems to be set up to calculate working hours and all projects have an end date/time. And then, the custom function is not at all similar to the one explained in the video. And that's probably because you are trying to count the number of hours.

And when I refresh the query it produces only errors in the added (invoked function) column, saying that "5 arguments were passed to a function that expects 3".

So, what are you actually trying to achieve here?

EDIT:

I was working on a Macbook Air earlier and overlooked something on it's small screen and with a different PQ look and feel. Now, on a big screen PC, I see that your query works fine and returns working days, though not hours! When I remove an end date and refresh the query the respective row in the added column remains empty.

So, back to your question. What do you mean by "Is there a way that these fields also remain empty?" ?

Hi,

well, you´re right, I´ve change the dateformat to whole days.

You are also right, the outcome is empty, but in backgound I´ve have an error 🙁

Is there a way, to get this fixed

Why did you choose to go the complicated way with List.Generate, creating tables and then merging them? The approach in the video is much simpler. But that's your choice, of course.

The error results from the fact that 'seconddate' is a null value when the end date is empty in the data table. PQ can't set a null to a data type 'Date'.

Change the last step to:

Hello Riny,

well, I just selected this "complicate" way, as this was just the first one, I found.

I have just a rough understanding of that code and this way seems working.

I am openminded to a better way.

Finally I just get a table with a start and an end date and I should count the working days.

You suggestion works fine, but I am openminded to a better way. 🙂

Glad it works then. Regarding the "better way", just follow the YT-video you included in your first post. It explains a much easier approach.

ohh, indeed, the function is somehow complete different.

I listened to the video, and downloaded the file, but I didn´t compare

Thanks for the remark.

Hello Riny,

well, I played some more about this, and noticed, that bigger lists take really long for calculation of that "easier way".

This was also mentioned here, whan I searched for a solution.

https://community.fabric.microsoft.com/t5/Power-Query/Optimise-Query-for-Calculating-Number-of-Business-Days/m-p/2216731#M65694

I noticed also their code differes a little bit:

(StartDate as date, EndDate as date) as number =>

let
    ListDates = List.Dates(StartDate, Number.From(EndDate - StartDate), #duration(1, 0, 0, 0)),
    RemoveWeekends = List.Select(ListDates, each Date.DayOfWeek(_, Day.Monday) < 5),
    RemoveHolidays = List.RemoveItems(RemoveWeekends, #"bank-holidays-dl"),
    CountDays = List.Count(RemoveHolidays)
in
    CountDays

 

according that from youtube:

(StartDate as date, EndDate as date, Holidays as list) =>

let
DateList = List.Dates(StartDate, Number.From(EndDate - StartDate) +1 , #duration(1, 0, 0, 0)),
RemoveWeekends = List.Select (DateList, each Date.DayOfWeek(_, Day.Monday) <= 5),
RemoveHolidays = List.RemoveItems(RemoveWeekends, Holidays),
CountDays = List.Count(RemoveHolidays)

in
CountDays

Do you know, which one is correct?

No I don't know what's correct, but remember the guy from the video mentioning that the +1 was needed because he wanted to include the start date in the count of days. I guess that it also impacts the <=5 or <5 bit.

Most important is that you understand the method and decide for yourself if you need the +1 or not. But since you mention that the "simple method" works very slow on your larger data set, I would stick to your more complex method that obviously works faster.

Thank you 🙂