Before we begin:
Today is the last day for enrolling in our Power BI Play Date. Don’t miss out on this amazing opportunity to learn, use and benefit from Power BI at your work. Check out my online class and sign up before the doors close at midnight. Click here.
Let’s get our Simpsons on then.
D’oh, How often Homer says his favorite things?
Here is the visualization to explore Homer’s (and other character’s) favorite sayings in 27 years worth of Simpsons episode. Have a play.
(Click here if you are unable to see the visualization.)
Where do we get the data?
To answer this question, we need to access Homer’s brain. It is in a high security vault, otherwise known as his skull.
But we can’t connect to Homer’s brain as data source in Power Query. What then?
I am kidding. We can simply Google for this. And we quickly land at Kaggle’s Simpsons Script Dataset. This contains 27 years of Simpson’s script, character, location and episode data. Download and load these tables in Power BI as tables.
Connect script table to characters, locations and episodes. Also,
- set up episodes[image_url] as Image URL data category thru Modeling ribbon.
- set up episodes[video_url] as web URL data category thru Modeling ribbon
Define a phrase list
Why stop with D’oh. Let’s make a list of all phrases you want to investigate. Using “enter data” feature of query editor (Power Query) create a list of phrases you want to explore.
Add this phrase list as a slicer to your Power BI visualization. Define a harvester measure to findout which phrase is clicked. Use
SELECTEDVALUE() for this. Let’s call this measure [selected phrase]
Count how many times characters said [selected phrase]
We can use SUMX, SEARCH to find out how many times [selected phrase] is uttered in all script text, like this:
Phrase Count =SUMX(script, IF(SEARCH([selected phrase],script[normalized_text],1,0)>0,1,0))
Visualize phrase count by characters, episodes and locations
Add a few vizzes (tables work great, but try others too) to see how many times each character spoke that phrase. Explore and Enjoy.
D’oh – Homer’s favorite sayings in Power BI – Video
I made a video tutorial explaining the entire process along with many tips on data clean up thru Power Query, measure writing and visualizations. True to Homer’s style, there is a d’oh moment in the video where I realize a fatal flaw in the analysis and fix it. Check it out below.
See this video on our Youtube channel.
Want more duff? Check out Power BI Play Date
If you love to play and learn, you are going to love our Power BI online class. In this program, you will learn all about Power BI from basics to #AWESOME level. You will get 9+ hours of video content covering Power Query, Power Pivot, Power BI and elegant dashboard design techniques. Check it out and sign up today.