Introduction
Workout Wednesdays for Power BI is now in our sixth month! Thanks so much for joining us. For the month of June, we will have independent exercises that all use the same data source but focus on different aspects of the data and of Power BI.
Our dataset for the month comes from Nobelprize.org. We’ll be visualizing and learning about the various categories of Nobel Prizes and the laureates to whom they are awarded. This data comes from a free public API that returns data in JSON format.
Our challenge this week demonstrates how Power BI can help us parse JSON data. We will combine some old and new Power Query and DAX skills to accomplish our visual goals. We’re going to use DAX to summarize the data about the awards to determine the first and last year each category of Nobel Prize was awarded and count the number of prizes given. Then we will use a stacked bar chart to make our own timeline visual. This visual is similar to a Gantt chart, but many custom Gantt chart visuals in Power BI cannot handle the span of over 100 years and aren’t very flexible in their formatting. Customizing a bar chart can be an option for a cleaner, more minimalist style.
Requirements
- Retrieve the data from the API using the following URL: https://masterdataapi.nobelprize.org/2.1/nobelPrizes?offset=0&limit=660 (Hint: Power BI can expand the dataset into a table for you!)
- In Power Query, remove any columns that end in “.no” or “.se” as well as any columns that begin with “links” or “meta”. (Hint: You can do this dynamically by creating a list of the columns that meet this criteria and supplying that list to the RemoveColumns function.)
- Use DAX to create a table that summarizes the data to get a table that contains one row per award category with the following columns: award category, award category full name, first year awarded, last year awarded, number of prizes awarded (equivalent to one per year).
- Create a visual that lists the full name of the award category along with the first and last year the prize was awarded. The example uses a table, but you can choose a different visual if you prefer.
- Enhance your calculated table using DAX to enable you to create the timeline visual shown above using a stacked bar chart. It must start at 1900 and end at 2020. Award Category should be on the Axis. A new column or measure will need to be created to populate the bars.
- Create a tooltip report that shows the category, first year awarded, last year awarded, and number of prizes awarded (1 per year) that appears when you hove over the data points in the stacked bar chart visual.
- Format your report with the layout, fonts, colors, and title according to your preferences.
Dataset
This challenge uses data provided by the Nobel Prize organization. You may reference the documentation at https://www.nobelprize.org/about/developer-zone-2/. This week’s data can be retrieved with the following url: https://masterdataapi.nobelprize.org/2.1/nobelPrizes?offset=0&limit=660. The data is free to use and contains information about the Nobel Prizes and the Nobel Laureates. The data is updated as the information on www.nobelprize.org is updated, including at the time of announcements of new Laureates.
Share
After you finish your workout, share on Twitter using the hashtags #WOW2021 and #PowerBI, and tag @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.
Great challenge. Learned to create summarization table using DAX!!