Introduction
First of all, I am honored to be able to deliver my first Workout Wednesday challenge. Hopefully this is just one of many challenges I can bring to you.
More importantly… Have you ever been able to easily find Rhode Island or Delaware on a US map? Yea, me neither. This is why I love Hex Maps! Finally, those miniature states can grow up and hang with the big kids like Alaska, Texas, and California. All kidding aside, I really love Hex Maps as I find it a much easier format to visualize location and comparative value of metrics across them. As such, I wanted to create a Hex Map in Power BI but I found that is not as easy as I originally thought. This challenge requires using a very unique and flexible tool called Synoptic Panel. This solution consists of an online tool combined with a custom visual in Power BI. It allows you to upload images to the online tool and then define areas of the image that can be segmented within the tool. In this challenge, we will be utilizing a 2019 dataset on US state migration to visualize the movement between US states.
Requirements
- Either download the Excel data files or utilize the data.world connector from the link, here.
- Load the file named “State_to_State_Migrations_Table_2019.xls”
- Load the file named “State Mapping.xlsx”
- Perform the necessary query steps to match the data found in the final data model.
- Fields: State To, State To Abbr, State From, State From Abbr, Population Moving
- HINT 1: Why join once, when you can join twice?
- HINT 2: Do not forget about the District of Columbia. It might disappear if you don’t pay close attention.
- If you prefer to start with a complete data model, please download the Power BI Desktop (.pbix) file.
- Import the custom visual, “Synoptic Panel by OKViz”.
- Build a Hex Map using one of the default templates to show the states people have moved most commonly from
- Use your own color scheme and have fun with it
- HINT 1: Only one State To can be evaluated at a time
- HINT 2: Knowing the order of the population moving by state might help
- Build a bar chart to show the top states people are moving from utilizing the same color scheme as the Hex Map
- Perform final formatting. Match my format or put your own spin on it.
Dataset
This week you can find the data here. You have the option to build the data model from scratch or start with the completed data model.
Share
After you finish your workout, share on Twitter using the hashtags #WOW2022 and #PowerBI, and tag @JSBaucke, @MMarie, @shan_gsd, @KerryKolosko, and @NerdyWithData. 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.
Hello,
Thanks so much for this! I love using this. I do have an important question…. How come MO (Missouri) is to the west of Colorado? How come it is not to the east of Colorado and Nebraska?
Thanks in advance!!
Hey Maria – wow, that’s a mistake – thanks for calling that out. I hope you’re enjoying the challenges!