PDA

View Full Version : Calculating Net working days using power query.



mitchandsuzy
2016-01-05, 06:35 AM
Before I start - this is the reference I used on Ken's Blog - Calculate Hours Worked - Ref Blog 10th June 2015.

Thanks to everyone for sharing your knowledge!! I have bought M is for Data Monkey and Matt Allingtons Power Pivot book to try to teach myself how to use these great add-ins.

I have modified the Code that Bill Szysz allowed us to look at in the blog above.

It works okay but it duplicates all the results - I can get rid of the duplications in PQ using remove duplicates - but that offends me - I want to get the answer right the first time. (Watching too much Mike Holmes!).

Is there a debug mode?? I am sure it is an issue with the joining method but I can't find anything to help online or in the books.

I will attach the file that Bill created and I modified.

Thanks for any assistance you can offer.

Suzy

Herbds7
2016-01-05, 04:45 PM
Remove Duplicates works for me.
See this book:
Power Query for Power BI and Excel
by Chris Webb

mitchandsuzy
2016-01-05, 11:13 PM
Thanks Herbds,

I will look for the book.

I am rather confused though - back in the 90s when I programmed in SQL getting duplicate records meant that I had stuffed up the join - so I assumed I had done something wrong in the calculations. It's just that if there is duplication that may mean there are other unintended consequences....

Suzy

Herbds7
2016-01-06, 01:32 AM
Here is a version that does not use functions,
does not run into duplicates and
requires no knowledge of M.
http://www.mediafire.com/download/b8d6fvwmb6my9tb/01_05_16b.xlsx

(http://www.mediafire.com/download/b8d6fvwmb6my9tb/01_05_16b.xlsx)

mitchandsuzy
2016-01-06, 03:22 AM
Thanks very very much Herbds,

I can use this to get my report done in the interim.

I still want to debug the function so if anyone else has any ideas - if I figure it out I will post as well.

Thanks again

Suzy