Wednesday, August 02, 2017

Analyzing Diners, Drive-Ins and Dives Data with Power BI

Neal Levin
BI and Analytics / Technology

I have a passion for data and the ability for Power BI to provide unique insights. I also happen to have an interest in unique food items and establishments. One of my favorite TV shows is Diners, Drive-Ins and Dives. I enjoy watching the show because it not only highlights a variety of dishes and recipes, but also celebrates what is great about America: our creativity, entrepreneurship and diversity.

One evening while watching the show, I decided to see if there was a way to apply my passion for data and Power BI to decoding Triple D. I explored the Food Network website and learned that each episode and associated restaurant is highlighted on a separate web page. Details for each restaurant including address, phone, website, and featured menu items are located in consistent areas on the web page template. This made it ideal to utilize a popular screen-scraping tool, to gather restaurant details and load the data to a CSV file.

I proceeded to load the CSV file to Power BI. I then utilized the Power Query capabilities of Power BI to standardize and shape the data into a common format. This included:

  • Filtering out restaurants that are designated as closed
  • Removing locations outside the USA in order to optimize geographic display
  • Removing duplicate restaurants that are featured in multiple episodes
  • Standardizing the address column and creating separate columns for state and city
  • Adding a conditional column that performs a “contains” search on the Featured Menu Items and creates a Cuisine category

Once I completed shaping/editing the data, I proceeded to prepare the report highlighted below by:

  • Creating a measure to count the number of restaurants
  • Utilizing the new Power BI matrix visual to create slicers for state and city
  • Leveraging the Power BI capability to convert a URL to a link icon
  • Optimizing the report for mobile view by using the Phone Layout feature of Power BI
  • Publishing the report to the Web

 

You can find the report here. One of the surprising facts I learned about the show, is that the two cities with the most featured restaurants are Minneapolis and Portland. I am proud to say that my home city of Chicago is number three on the list. You can filter the list and map by clicking on the applicable state and city. Clicking on the link icon takes you the restaurant home page.   Feel free to try this out and I would welcome any feedback.

There are a number of capabilities and features that I would like to add including:

  • Filter the data by GPS coordinates of the mobile user
  • Include photos and links to videos
  • Provide recipes

Furthermore, I think this process could be applicable to a number of other business needs including obtaining competitive intelligence and gathering news, weather, or other relevant topics.

Let us help you drive data and find insights. Learn more about our Business Intelligence & Analytics capabilities.


Neal Levin is the Vice President of Business Intelligence & Analytics at Rightpoint. Follow Neal on Twitter and LinkedIn.

Loading Next Article