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.
14 Responses to “SUMPRODUCT Vs. Power Query on Mt. KauKau”
For the "Output New" worksheet, there is no need for the formula in column G - do it in Power Query
1. Click the "End" Column
2. Holding CTRL, click the "Start" Column
3. On the Add Column tab, choose the "Time" group, then "Subtract"
The duration in hours is calculated - no need for the IF statement in column G of the "Output New" worksheet 🙂
Note though, that the "End" column in the Power Query must be selected first then the "Start" column. This also works when calculating number of days between two dates
Chandoo, that is a fantastic practical demonstration of power query. I have all but ceased using formulas and VBA for ETL (Extract, Transform, Load) work after discovering Power Query. It is by far my favourite tool for preparing data for uploading into Business Intelligence systems. Thank you for creating this article showing the conversion of an irregular data source.
' why not just VBA on the Schedule sheet...
Private Sub CommandButton1_Click()
Dim RaDa As Range, RaOut As Range
Dim Ri%, Ci%, Ro%, EmpNum%, HoursW!
Set RaDa = Range("a1").CurrentRegion
Set RaOut = Range("a24").CurrentRegion
' zero total hours
For Ro = 2 To RaOut.Rows.Count
If RaOut(Ro, 1) "" Then RaOut(Ro, 2) = 0
Next Ro
For Ci = 3 To RaDa.Columns.Count Step 3
For Ri = 3 To RaDa.Rows.Count
If WorksheetFunction.Proper(Left(RaDa(Ri, Ci), 6)) = "Employ" Then
EmpNum = Val(Right(RaDa(Ri, Ci), 2))
End If
If EmpNum > 0 Then
HoursW = (RaDa(Ri, Ci + 2) - RaDa(Ri, Ci + 1)) * 24#
If HoursW <= 0 Then HoursW = HoursW + 24
RaOut(EmpNum + 1, 2) = RaOut(EmpNum + 1, 2) + HoursW ' + 10
End If
Next Ri
Next Ci
End Sub
I worked through the video and the exercise.
The sound on the video was very low for my laptop!
I did some of the work in the Excel Table before I sent it to Power Query: the headings are a mess and by promoting Location, Post, Date/Employee name etc to the header row, you save steps in the Query: it saves a lot of effort.
Otherwise, I think I did Just about the same as you and got the same outputs apart from this:
all of your employees worked 8 hours for every shift: I tested my work by changing some of them.
Rather than doing the Duration calculation in the Query output table, I created a Custom Column in Query to calculate Duration (Hours) with ([End]-[Start])*24
Good exercise Chandoo and thanks for sharing!
By the way, I did not bother with SUMPRODUCT and so on because that initial table is just a complete mess!!!
I would say: why VBA when you have the power of, dhuh, Power Query/Get and Transform?
So I'm 100% with Leon-K: I've banned VBA (almost completely).
Regarding the excercise, which is a great example of rather messy data one comes across in real life. I would be tempted to use the fill down in all columns with data gaps instead of merging and splitting again. But I have not checked what the result would be.
As alternative for the calculation, send the PQ to PowerPivot and add a calculated column using DAX. But that is just adding extra steps. I follow Graham and Duncan here.
[…] http://chandoo.org/wp/2017/06/15/sumproduct-vs-power-query/ […]
I am - and have been - enjoying your blog for some years now. Apart from your acumen and your expertise in Excel I find your examples and explanations very helpful.
Since that has been written all over the place by a huge number of people, allow me to give less positive feedback as well:
Since you have been successfull your posts tend to focus more on yourself, your life situation, family, nature and vacations. This has very little if anything to do with Excel. I find this obtrusive and undue.
As part of that, you start explaining articles in a video. That makes it so much harder to understand what you are trying to explain. Not everybody is as fluent in english, nor is it everybodys mother tongue. A written explanation can be read a sentence at a time, translating words as necessary. Rewinding and re-listening to a video for a word that cannot be understood because of dialect or improper pronunciation making this an extremely aggravating experience.
I do respect that this is your blog; it is yours to decide what to post. But I'd find it more respectful to differentiate between personal blog and Excel expertise. For the latter, not forcing people to look at a self-aggrandising promotional video would be respectful to the readers that are part of your success.
I know that sounds harsh. I am sad to be the one writing it.
@Wolf... Thanks for your comment and love. I understand your concern. Power Query is more tricky to explain than Excel charts or formulas. That is why I rely on videos when explaining something like this. Imagine writing and illustrating all these steps in a text blog post. It would be several pages long.
Regarding sharing personal stories in the posts, I will watch the frequency and moderate it. I usually share a snippet of my life once every 10-12 posts. Sometimes it will be more often depending what is happening in my life (like the recent hike with Jon Peltier is too good to not share).
@Wolf
"forcing people to look"
You are not forced to do anything you don't want to do, if you find the content of Mr Chandoo's site so abhorrent then you are free to go elsewhere. This is not the only Excel site on the web. You going elsewhere would be infinitely preferable than insulting the man on his own site.
Constructive criticism is fine, and your comments on the merits/demerits of video are of course valid as you are entitled to your own opinion on this. But you cannot state that you respect that this is Mr Chandoo's blog and then proceed to disrespect him by telling him that what he does is wrong and accuse him of being self-aggrandising on his own site that he built up himself through his own efforts, time, and money.
And for the record, you do not speak for all of us, and are in no position to tell Mr Chandoo what would or would not be respectful to us. Personally, I happen to like hearing about his life and his life stories.
I rarely ever comment on posts despite also reading it for years, but I will not stand by and allow you to insult Mr Chandoo in our name with impunity.
@Wolf
Chandoo.org started life as Chandoo's Personal Blog
It ran for several years before Excel appeared in 2008
Those of us whom have been here for a long time have seen Chandoo' Blog and Life, Married, Kids, Car, House, Biking, Overseas travel, Holidays and overseas move, develop in front of us and we are appreciative for it. Excel is rather bland by itself.
You may wish to start at Chandoo's first post, 1 July 2004, http://chandoo.org/wp/2004/07/01/and-thus-born-modus-indoramus/
and work your way forwards
Excel is rather bland by itself? Goodness, i couldn't agree less Hui!
I owe my career and personal success to Chandoo's site, where studying Excel led to me becoming a senior BI developer in a major company. I consider myself, in part, responsible for his success and new life in New Zealand. It is a brave thing to demonstrate what success looks like by sharing your personal life with those responsible for it. But, I'm proud of what I've achieved for myself in five years and I'm proud to be a part of Chandoo's happiness too. That is karma. I know many successful people that would not dare credit anyone and flaunt their wealth in disgusting way. Chandoo, it's your site and I choose freely to read it and buy your masterclasses. Peace and love to you and your family. I hope your success continues to bring you happiness.
Excel is rather bland by itself? Except when it is touched by the Hui's and Chandoo's of our world. Then it becomes spicy, stimulating, energetic and fun! (Sounds too much as a fan, right?)
- I also like videos more over long articles to learn from... At least you see how it is done. Even if sometimes it is indeed harder to understand the lingo. I've heard way worse accents ... and still learned something from it.
- Since there is so much to read here on Excel, I heardly notice the personal blog entries. But without the occasional "I-went-on-a-bike-tour" this site would be less Chandoo'ish. And that would be a shame.
- At least one can appreciate expert knowledge is shared for free. Personally I was, and still am, very inspired by this community and its wit, charm and stunning content, when I joined somewhat 2-3 years ago. To that point that I started my own "art of Excel'ence" blog in the company I work for. And I admit, I've "stolen" some of the concepts of chandoo.org. That happens with good examples. Most certainly when genuine art is involved ... 🙂
- So to all: keep doing this.
How to hide formula in cell & entire sheet.