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.
2 Responses to “Top 10 Power BI Interview Questions & Answers”
Hello...
In Power BI I have data that includes months by name only (e.g. May, April, December...)
I need to build charts etc. but i need the months to go chronologically... not alphabetically... I cannot seem to find the fix to this.... once again, my data does NOT have an actual date attached to it (like 02/01/2023)....only month names... can i use a helper table wher i id the month names as numbers 1 thru 12? and if so, how do i manage this to work for me ?
Thank you.
~Keith
You need to setup an extra table to map each month name to a running number. A simple 12 row table like
Jan 1
Feb 2
Mar 3
..
Dec 12
Then create a relationship between this month table and your month column
Now, go to "table view" in Power BI and set the sort by column to month number for the month name column on this new table.
Finally, use the new table's month name whenever you need to refer to the month name in the visuals.
They will be chronologically arranged.