2021 Week 34 | Power BI: Building a Network Diagram

Power BI, Workout Wednesday / By

Introduction

This week’s Workout Wednesday we will be creating a network diagram in Power BI. 

Network diagrams are useful to explore relational datasets and are often used to visualize such things as project interdependencies, and social networks. 

For this challenge we will be visualizing the interconnections of actors/characters of the Love Actually movie by number of shared scenes.

The dataset we will be using was created by FiveThirtyEight, for this inspiring Data Story: https://fivethirtyeight.com/features/the-definitive-analysis-of-love-actually-the-greatest-christmas-movie-of-our-time/

Requirements

  1. Download the Love Actually dataset or connect via the DataWorld connector. https://data.world/fivethirtyeight/love-actually 
  2. Import the Microsoft Network Visual from AppSource
  3. Transform the data in Power Query so that there is a source and a target i.e. pairs of actors appearing in the same scene.
  4. Create the network diagram by selecting the fields for the actor pairs and number of shared scenes. Be sure to avoid self-referencing loops or links with zero value. Feel free to add your own flair.
  5. Add a filter to the report page to filter the network diagram by scene
  6. Answer the question, who do think is the most central actor/character?

Extra:

  • Create measures to calculate the actors with highest number of connections, shared scenes and appearances.

Dataset

https://data.world/fivethirtyeight/love-actually

Share

After you finish your workout, share on Twitter using the hashtags #WOW2021 and #PowerBI, and tag @kerrykolosko, @JSBaucke, @MMarie, @shan_gsd and @dataveld. Also make sure to fill out the Submission Tracker so that we can count you as a participant this week in order to track our participation throughout the year. 

Solution

Hint:

  1. Scenes occur in the order of which they are listed. Add an Index column starting from 1
  2. Unpivot the actors’ names
  3. GroupBy scene number and scene name to create a nested table
  4. Duplicate the column of nested tables
  5. Expand the actors’ names to rows for both columns
  6. Create a custom field with the logic [actor 1] >= [actor 2] to identify duplicate pairs
  7. Create a column with the count of actor adjacencies for each scene
  8. Create a column with the count of actor appearances for each scene
  9. If needed, the Power Query can be obtained here
Scroll to Top