This week’s Workout Wednesday we will be creating a network diagram in Power BI.
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/
- Download the Love Actually dataset or connect via the DataWorld connector. https://data.world/fivethirtyeight/love-actually
- Import the Microsoft Network Visual from AppSource
- 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.
- 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.
- Add a filter to the report page to filter the network diagram by scene
- Answer the question, who do think is the most central actor/character?
- Create measures to calculate the actors with highest number of connections, shared scenes and appearances.
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.
- Scenes occur in the order of which they are listed. Add an Index column starting from 1
- Unpivot the actors’ names
- GroupBy scene number and scene name to create a nested table
- Duplicate the column of nested tables
- Expand the actors’ names to rows for both columns
- Create a custom field with the logic [actor 1] >= [actor 2] to identify duplicate pairs
- Create a column with the count of actor adjacencies for each scene
- Create a column with the count of actor appearances for each scene
- If needed, the Power Query can be obtained here