When faced with tough problems I react in one of three ways
- Come up with ingenious solutions
- See if a simpler cheat solution is possible
- Sit back and ignore
For most problems, I choose 3rd reaction. Occasionally, I rely on 2nd option and very rarely the first one.
When faced with a tricky time sheet summary problem (as outlined below), after initial lethargy I wanted to solve it.
Time sheet summary problem
Imagine some time sheet data as shown below. What is the total time each employee worked?
The ingenious solutions
Of course, we can whip up a SUMPRODUCT concoction strong enough to knock off Mike Girvin unconscious* for a few milliseconds to answer the question.
As shown on this forum thread, there are several fun, creative and smart ways to answer each employee’s total duration using such complex formulas.
* Stop kidding yourself. Mike’s tougher than SUMPRODUCT.
The smartypants solution
We can cheat by first reshaping the data to something like this.
But how? Simple, we take a saw and chisel to the data. I meant power query of course.
SUMPRODUCT vs. Power Query on Mt. KauKau
Last Friday, I took a wee walk up to Mt. KauKau to enjoy the views and workout my calf muscles. I couldn’t help but share that beautiful moment with you all. So I recorded a video showcasing beautiful views and introducing the problem. Then, I recorded rest of it from the comfort and warmth of my home. So here we go:
Watch it on our YouTube channel.
Download Example Workbook with all the Power Query steps
Click here to download the example workbook. Edit any of the queries to see all the steps. Try replicating them yourself to learn more.
SUMPRODUCT or Power Query – Which one do you prefer?
Let me confess. My first solution to this problem is based on SUMPRODUCT. But that is because I didn’t have Power Query on that computer. But I later tried solving it with PQ and I just love the simplicity and power of that solution. Anytime I am wrangling poorly shaped data, I use Power Query instead of a mashup of formulas.
What about you? Which option do you prefer and why? Please share your thoughts in comments.
