Have you Heard This Joke: Gil, Austin, and Rob (left to right) Walk Into a Bar…
(Yes, Rob’s hair was purple at the time.)
(Note from Rob: and it’s still purple today!)
Intro from Rob
Well folks it’s an exciting time to be working in our field. An ongoing upsurge in Power Pivot awareness coupled with Microsoft kicking the Power BI marketing machine into high gear means three things: 1) We are very busy. 2) We are having lots of fun. 3) We need more people.
(BTW, have you filled out our mini-application form? That’s the primary route via which we add folks to our team, and we have also quietly started matching people with jobs elsewhere in the industry as well – connecting capable and under-utilized people with employers that need and appreciate said talent is another way to “increase the happy” in the world).
And people… well, the people in our field are the BEST thing about it! Years ago, when one of Microsoft’s competitors attempted to woo me away from the Microsoft orbit, I politely declined and cited “the Excel people” as the reason I was never leaving the MS ecosystem. Not the Excel team at MS mind you, but the folks who USE Excel every day. I love you folks. Furthermore, I am one of you, even though I was formerly one of the MS folks. As was Avi. And Jocelyn. And Scott.
And now… Gil Raviv! Just an amazing dude with tons of talent and energy, and also (until recently) a Microsoftie. We are very fortunate to be able to add someone like him to the team – he brings world-class Power Query and M knowledge to the table, absolutely rocked my DAX interview puzzle, and offers a perspective and sense of humor that is unique. In many ways he epitomizes that blend of hard skills (DAX, M) + soft skills (people, comprehension) that we see as the future of analytics and BI. Time for his first blog post! Oh wait, he posted here a long time ago while he was still at MS… oh well, let’s call it his first anyway for fun.
Take it away, Gil…
Joining P3 Adaptive , and the Star Wars Slicer Brain Teaser!
Hi everyone. My name is Gil Raviv. Some of you may know me from my blog, DataChant.com, where I share Power BI, Power Query & M tutorials. Two weeks ago I joined P3 Adaptive team as a Principal consultant, and I am tremendously excited about it.
It is not the first time that I’ve blogged on P3 Adaptive . My first blog post here was written when I was a Program Manager at Microsoft. I found a cool way to integrate LinkedIn data into Excel, and Rob gave me the opportunity to blog about it. It was that first blog post that had awakened the blogging monster in me, and paved my way in the days to come.
In today’s blog post, I would like to share with you a brain teaser on Power Pivot and Power Query, that you can try on yourself and/or colleagues (as long as they don’t read this blog, right?)
But before we drill down to DAX, M and in between, here is how I ended up on the glorious P3 Adaptive team…
The Microsoft Days
In December 2013 I joined Microsoft as a Program Manager on the Excel team. I was assigned with the mission to improve Power View in Excel Online and Excel 2016. Ironically, at the end, the main improvement was to “turn off the lights” on Power View, and remove it from the Excel ribbon (You can still turn it on though, more info here). As the Power View team moved their efforts to new cloud experiences, and paved the way for Power BI, I was lucky to switch roles and start working on Power Query.
Leading the integration of Power Query into Excel 2016 was an amazing experience for me, and I got to work with some of the most talented people I have known. We re-wired Power Query into the Excel Data ribbon. Power Query in Excel 2016 (now squeezed into a small ribbon chunk called “Get & Transform”) was no longer an Add-In, but a true Excel powerhouse, that gracefully plays its part inside the bigger Excel ecosystem (For example, you can run VBA to build new queries and edit their formula language. Read more on Power Query integration here).
Power Query’s new location in Excel 2016.
During that assignment, I became obsessed with Power Query and M, and gave very little attention to Power Query’s bigger sibling, Power Pivot.
My Love-Hate Relationship with DAX
Well, it started as a hate relationship. While it was clear to me that Power Pivot and DAX were extremely potent, I was too lazy and undisciplined whenever I tried to learn DAX. Building relationships was easy enough. Performing row-level calculations was straightforward. But whenever I heard the word “CALCULATE” in the corridor or classroom, I shied away.
But the change started when I was working on a new template for Excel 2016 (read more here). I was stuck with a DAX measure and called one of my colleagues for the rescue. As I was jealously watching him striking my keyboard, and creating the necessary measures, I experienced the magic of Power Pivot for the first time. Determined to get it right this time, I decided to listen to my colleague’s explanation, and I finally got what was CALACULATE all about.
My first real DAX Experience: Building this Template Back at Microsoft
The next day, I started reading the first edition of Rob’s book, and gradually let the DAX-phobia fade away.
Sidetracking Tip: Power Pivot is 1000% more powerful when combined with Power Query. The two tools didn’t live in harmony in Excel 2010, but combined together in Excel 2013, Excel 2016 and Power BI Desktop, Power Pivot and Power Query redefine Self-Service BI, so upgrade your Excel and make sure you take Power Query into consideration next time you build new Data Models.
Leaving Microsoft and How I Ended Up Here
After two years at Microsoft, and playing a significant role in releasing the best (IMHO) feature in Excel 2016, I left Microsoft, moved to Chicago, and invested a significant portion of my time in blogging on Power BI, Power Query and Power Pivot.
I would probably stay at Microsoft on the Excel team if I could, but there was no development center in the area, and the best advice that I got from my managers was to contact Rob. So I did.
That was six months ago. At the time, I was still solely focused on Power Query, but Rob gave me some good advice: “Improve your DAX”. He also offered to send me a test in DAX, but at the time I was still psychologically ambivalent about DAX, and I was still waiting for my US work permit, so I didn’t really insist on getting that test.
Time flew by. I had been improving my DAX and when the US work permit was finally in my pocket, I was ready for Rob’s trial. So Rob sent me the test. It was shockingly challenging, and it took me three sleepless nights to pass it. And here I am 
Wanna Know Rob’s Interview Test?
Sorry mates. You don’t really expect me to share Rob’s test, do you? And don’t try to seek it on Glassdoor either. Instead, I will share my own brainteaser.
The test starts with a kind of a silly challenge, but you need an end-to-end above-average experience to implement it.
The Brainteaser – Add an “ALL” Button to a Slicer
The idea for the test, came during one of my first consulting sessions, when I was asked the following question: How can we add ALL as a button in the Slicer, as illustrated below?
Can we Add an “ALL” Button to Our Slicer that Yields the Same Results as the Clear Filters Button?
Most of you are probably asking “WHY?”. The Slicer already has the Clear Filter button at the top right corner, so isn’t it a bit silly? The client DID have a legitimate reason for needing it, but it was a pretty obscure and domain-specific need. For the rest of us, it still is an engaging puzzle!
Note from Rob: aha! But there IS a more general case in which we need this – when we turn off the Slicer Header, we lose the “clear” button, as pictured here:
If you turn off the Slicer Header, You Lose the Clear Filters Button – Making this Technique
More than Just a Nifty Brainteaser!
So let’s insist on having an “ALL” button, and require that the “ALL” button will be the top button. To make the test more interesting, let’s assume we already have a measure that summarizes a numerical column in the Fact Table, so we will need to fix that measure as well.
BTW, the entire test can be done on Power BI Desktop as well.
The Star Wars Slicer Test
I prepared an Excel workbook that you can use for the test. It is based on a Star Wars Web API that I often use for demonstrations (More info here).
You can download this workbook as the starting point for the test. It contains three tables in the Data Model:
- Species
- Planets
- Characters
Each Star Wars character belong to a species, and has a homeworld (a planet). The three tables are connected as follows:
We use the following measure to estimate the average mass:
Average Mass := AVERAGE ( Characters[Mass] )
In the main worksheet we have PivotTable with the Planets, and the average mass of their Star Wars characters. Our main task is to add “ALL” as the top button in the slicer.
To make the test challenging enough, and to be picky with our job candidates, I’ve added three queries (Characters, Planets and Species). We will expect the interviewee to do some M work to resolve the test.
The Solution
Spoiler Alert!!! You may want to take a break and try answering the test by yourself before you continue reading.
Part 1 – Append ALL to Species
We will add the following table to Power Query and append it to query Species.
| Species | ID |
| ALL | 0000 |
There are multiple ways to do this step. If you want to test your candidate’s Power Query skills, challenge them to start with a blank query, as I will show you now:
Creating a Blank Query
In the Query Editor’s Formula Bar enter the following formula:
= {[ Species=”ALL”, ID=”0000” ]}
In List Tools, Transform tab, click To Table…
Click OK in the next dialog…
Expand Column1, uncheck Use original column name as prefix, and click OK.
Rename the Query1 to ALL, and open query Species.
Click Append Queries in Home tab.
In the Append dialog, select ALL as the table to append, and click OK.
Sort column Species in ascending order, and find ALL in the appended table. You can also see ALL as the second item in the filter. Which brings us to the next part, where we make sure that ALL will be the top item in the Slicer.
Sidetracking Tip: Power Query is an ideal tool to create a lookup table in your Data Model. You can use it extract the relevant column from the fact table and remove duplicates before you create the one-to-many relationship. Remember: Power Pivot is case insensitive, and Power Query isn’t. Make sure that all the values on your lookup tables are lowercased before you remove duplicates. Make sure you trim all the values in the lookup table before you remove duplicates. If you have values such as “customer1 “ and “customer1 “ (with a trailing space) Power Pivot will treat those values as duplicates.
Interested in Learning How to Do this Kind of Thing?
Our New Online Power Query Course just launched this year. You’ll learn Power Query from the best in the business, two Excel MVPs Ken Puls and Miguel Escobar. We’ve included 7 Hours of Self-Paced Video over 31 Courses, with lots of example files, and a Digital Badge powered by Acclaim.
CLICK HERE to claim your spot.
Part 2 – Create a new column for the sorting order
Our task now is to “push” ALL to the top of the Slicer and keep the alphabetically sort order for all the other elements in the Slicer.
Let’s assume that we will never have a Species that starts with the letters “aaaa”, and proceed with the following steps:
While the Query Editor is still open on query Species, click Add Custom Column in the tab Add Column.
In the Add Custom Column, set New column name as Sort Order. Next, paste the following formula to the formula box:
if [Species] = “ALL” then “aaaa” else [Species]
When you are done, click OK.
We can now go to Home tab, click on Close & Load drop down menu, and select Close & Load To...
In Load To window, select Only Create Connection, and check Add this data to the Data Model. Click Load when you are done.
We finished the Power Query elements of the test. Congrats!!! Time to move to Power Pivot.
In the Power Pivot window, open the table Species, and select the column Species.
In Home tab, click Sort by Column, select the column Sort Order in the drop down menu Column, and click OK.
That’s is, ALL is now the first button in the Slicer. But the measure [Average Mass] is obviously not working as required when ALL is selected. We will fix it in the last part.
Part 3 – Fixing the measure
Our measure is still not “wired” to recognize ALL as needed. Our Characters table has zero rows with ALL as their species. In this part, we will change the original measure to recognize the scenario when ALL is selected.
Open table Characters and add the following two measures under [Average Mass]
[All Species Avg]:=
CALCULATE([Average Mass] ,
ALL (Species[Species])
)
[Avg Mass Fixed]:=
IF (
CONTAINS(VALUES(Species[Species]),
Species[Species],
”ALL”),
[All Species Avg],
[Average Mass]
)
The measure All Species Avg clears the Species values from its filter context, and measure the average for all the species.
The measure Avg Mass Fixed gets all the selected items in the Species slicer, by using the VALUES function on table Species and its column Species. Then we check if the slicer contains an item “ALL”, by using the CONTAINS function. Using IF, we apply the original Average Mass measure when “ALL” is not selected, and the new All Species Avg when “ALL” is selected.
We are almost done. Now you can go back to the PivotTable. Drag and drop Avg Mass Fixed into the Values pane in the PivotTable pane, and remove the original measure Average Mass.
It works!!!
One more time: you can download the Start or Finish versions of the workbook at your convenience.
Conclusions
While the solution above is not recommended on real world scenarios, as we already have a Clear Filter button, I hope you will find it useful as an assessment test. Just ask your candidate if he knows the P3 Adaptive Star Wars Slicer test, and if not, well, that’s probably OK right? Even Power BI Padawans can move entire planets.
I am looking forward to meeting you again on my future blog posts on P3 Adaptive or DataChant, and who knows, perhaps we shall meet next time you hire a P3 Adaptive consultant (here or here).
