Introduction
This week we are collaborating with Preppin’ Data to get a close look at the schedule for the 2020 Olympic Games! We’re also collaborating with the Tableau team and we invite you to take a look at both challenges! With the opening ceremony 2 days away, we thought we’d visualize the schedule in our own time zone.
We encourage you to take a look at all challenges this week to see the similarities and differences.
July is also Power Query month – which you’ll use to clean and shape the data prior to building your report.
Every Power BI challenge requires Power Query to get and transform data, and we’re diving a little deeper this month! If you haven’t checked out the other Power Query challenges, you can find them here: Corporate Financials and Web Scraping.
This week we are going to create parameters for local files, translate data to our time zone, and create a calendar table using M!
Requirements
- Use Power Query to ingest data from the Preppin’ Data site.
- In Power Query, create a parameter for the report path URL (this is an optional exercise to simplify working on a local file with multiple report authors).
- Clean up the source data by:
- removing unnecessary columns.
- cleaning the text in the Sport Group and Sport columns.
- putting the event time/date in your own time zone! (source data is in UK time)
- Create a calendar (date) table in Power Query containing the following columns:
- Date
- Short Date (containing only day and month)
- Month Name
- Day of Week
- Day of Games (with day 1 on the day of the opening ceremonies)
- Week of Games (with week 1 starting on Sunday the 25th)
- See the sample report to clear up the confusion with Weeks 0, 1, & 2.
- Create a Gantt chart of your choosing to display the event schedule. In the sample report I used a matrix visual with conditional formatting on the background and the text.
- Adjust the size of the report canvas to fit the schedule on one page with no scrolling.
- Create a report page tooltip displaying details about the daily schedule.
Dataset
This week’s dataset comes directly from our friends over at Preppin’ Data.
Use it as is (stored locally) or put it online (Excel online or Data.World) – report designers choice!
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.