Log In
Register
Home
January 12, 2021
Offline
Hi all,
I have a struggle with this tutorial:
Power Query - Net working Days in power query (youtube.com)
Sample file is here:
powerbiTutorials/PQ_Working Days_2_Video.xlsx at main · jbotes/powerbiTutorials · GitHub
Here we have a table, where we can remove the weekends between a start and an end date.
Unfortunately, on my table, some “projects” are still ongoing, so that I don´t have an end date filled.
This fact results into an error, which I can´t fix.
Is there a way that these fields also remain empty?
See Answer
Moderators
January 31, 2022
Offline
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?" ?
January 12, 2021
Offline
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
Moderators
January 31, 2022
Offline
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:
Answers Post
January 12, 2021
Offline
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. 🙂
Moderators
January 31, 2022
Offline
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.
January 12, 2021
Offline
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.
January 12, 2021
Offline
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.micro.....731#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?
Moderators
January 31, 2022
Offline
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.
1 Guest(s)
