Olympics Data Analysis and Dashboard:
Power BI Data Preparation and Dashboard Development
Summary
With Power BI "Transform Data," clean and prepare the Olympics data set. Then, with Power BI Desktop,
plan and develop at least 4 professional quality dashboards plus a sheet that allows the user to drill down
to lowest-level details contained in the data, to help someone generally knowledgeable about the
Olympics, explore and learn more about:
a) the Olympics as a whole (dashboard);
b) the Olympics in a specific year and season that the user specifies (winter or summer)
(dashboard);
c) country-level information (user specifies the country) (dashboard);
d) sport/event-level information (specified by the user), including information about individual
athletes (dashboard); and
e) a page in the PBI file that allows the user to interactively drill down to the lowest level details in
the data.
Prepare a brief writeup as part of the PBI file.
Guidance
Read the entire document before starting. Plan to go through two or three iterations of your dashboards.
Start early enough so you can be thinking about how to improve the dashboards and make the document
unified (i.e., common styles, look/feel, user interactions).
Data Files and Making Connections to Data
The zip file olympics.zip contains three CSV files. Two contain information about Olympic Games
performances over many years (olympic_summer.csv, over 220,000 rows; and olympic_winter.csv, over
48,000 rows). These two files have the same schema (format/structure of data columns). The third CSV
file (olympic_countries.csv; about 230 rows) contains country abbreviations and associated country
names (see the Data Dictionary section below). Do not make any changes to the CSV files. Connect to these
files from Power BI. You can certainly look at and explore them in a text editor and/or Excel, but when
you load them to Power BI, connect to them as CSV files. It is best to keep the CSV files and your PBIX file
in the same directory.
It is important to realize that the PBIX file contains connections to data files. The PBIX file format is
designed to both keep this connection information in addition to a copy of the data. However, the “true”
source of the data is the CSV files, and whenever PBI refreshes the data (for example, in the Transform
Data steps), it looks to the connection information. You will likely be working on this from several
different computers. It is important, after copying the PBIX file to a new computer, to also update the
"Data Source" settings in PBI. From the Home menu, pull the arrow down next to Transform Data, and
choose "Data source settings." From there, select each CSV file and update the file location to where the
file resides on your computer. This is the most important during the Transform Data phase (data
preparation). You will not be able to do any Transform Data operations unless PBI can "find" the true
source files. After your data is completely prepared, PBI can actually work with the copy of the data in the
current PBIX file, but if the source data ever changes or you need to go into Transform Data again, you will
need to update the Data Source settings so PBI can find the files.
More information, not required if you follow the steps in the previous paragraph. It is possible to store the
CSV files on a web server or shared resource, e.g., Google Drive, Sharepoint, etc. In my testing with these
files, connections to Google Drive became quite slow due to the file sizes, and Sharepoint adds some
Olympics Data Preparation and Dashboard
Page 1 of 6 complexity that is beyond the current scope of this case. If you do choose to try this approach, make sure
that the source files are readable by anyone...me...without needing a username/password.
Tasks (see also the “Requirements for the Dashboards” section, below)
Note for dashboards: After your clean/prepare the data, you will have 4 tables: summer, winter,
combined, and countries. For the dashboards you will only use fields from the combined table and the
countries table. The purpose of the combined table is so that we can have combined summer & winter
results when desired, and just as easily filter out one season or the other when we don't. Do not create
visuals with the winter and summer tables.
1. Prepare/clean the data (see Data Preparation Guidance, below).
2.
Explore/Experiment. Spend time exploring the data by creating a number of visuals, tables, etc. Create
one or more "Experimental” pages for this. Do enough of this to learn major aspects/insights you want
to explore further, as well as nuances of the data that may not be obvious at first.
3. Overall Dashboad. Create an overall (i.e., top-level) dashboard (name the page "Overall"). The visuals
must show at least the following: medals awarded over time, athletes competing over time, medal
count by country, and athlete count by country. Include card(s) to summarize key values. You may
want to present some of this on the same visual(s). Use slicers and/or filters to allow user to drill into
the data as they choose to. Note that “athletes” competing over time can be interpreted several ways,
e.g., unique people who have competed ever, unique people for each Olympic games, and total number
of entrants into all events (this last one multi-counts athletes as people). Be clear in your visuals (on
this page and all) whether you are talking about unique people, or event entrants.
4. Year/Season Level Dashboard ("Year and Season Persona"). For this dashboard, have the user select
the year(s) and season(s) (use slicers or filters), and your dashboard will then present visuals that
inform the user about that specific year(s)/season(s).
5.
a. Note: For the dashboards in steps 4, 5, and 6, the idea is that the user will choose a specific
year(s)/season(s) (or country(s), or sport(s)/event(s)). The visuals then should show details
about that specific year/season (or country, or sport/event). Therefore, you would not want a
visual, for example, that shows medals over time for the year/season dashboard, as most of the
time the user will just be selecting a single year/season...that would make for a chart with one
data point, i.e., without meaning. Think about what is meaningful, test it out with various settings
as the user would do, and iterate; do not just click the buttons and accept defaults.
Country Level Dashboard ("Country Persona"). Provide a way for the user to select a country (or
countries), and then present information about that country and its participation in the Olympics.
6. Sport and Event Level Dashboard ("Sport and Event Persona"). Provide a way for the user to select a
sport(s) and event(s), and then present information about that sport/event. Within sports, there are
events, so this dashboard is more of a sport- and event-level dashboard. Ideally, the user should be
able to drill down to specific events and see specific information on individual athletes.
7. Drill-down sheet. On this sheet provide a way for the user to intelligently and seamlessly drill down
into the data to any level they wish, even to the individual record level in the data. You will probably
need to experiment with a mix of graphs, decomposition tree, and table/matrix (a combination of a
decomposition tree and a table or matrix is often a good way to provide drill-down capability).
8. Writeup. Create a "Writeup" page. Insert a text box. List/discuss three key insights you gained specific
to the problem context (that is, what did you learn about the Olympics). Then list/discuss three key
things you learned about visualization and/or Power BI in completing the assignment. Target length
for this is the equivalent of a one-page writeup (more than just bullet points...give the main point and
then explain, with examples to illustrate).
9. PBIX and PDF. Save your Power BI file as a PBIX file. Also generate a PDF of your PBI file by using the
File...Export option. You will be submitting both. You do not need to submit the CSV files.
Olympics Data Preparation and Dashboard
Page 2 of 6 Guidelines and suggestions for the dashboards
Spend time on the general layout and formatting of your first (overall) dashboard. That way you can
duplicate the page and make changes for the other dashboards. While obviously you will make
changes for each dashboard, there should be a reasonably common look/feel to your dashboards.
Try to make visuals professional quality, titled/labeled appropriately, with appropriate color
selections, and able to be understood by user without additional explanation. With visualization,
getting something 70% done can be pretty quick, but the remaining 30% of tweaking settings, titles,
colors, alignment, etc. is what often separates a professional-level job from a novice job. Slicers and
filters should add meaningfully to the dashboard's value.
Each dashboard should contain at least 4 visuals (for this count, slicers don't count as a visual, but you
should have one or more slicers also; table/matrix does count as a visual). Each dashboard must have
a brief text header for the title of the dashboard (put this in a text box).
Be precise about language. For example, "athletes" (unique competitors) is different than "event
entries" (all athletic entries in a competition), is different than "medals awarded," and is different than
“medalists” (unique athletes who won at least one medal). For many/most charts, you will need to
change the default titles to ensure the user knows exactly what the chart displays. You can distinguish
between these (depending on the field) by summarizing as a Count versus the Count Distinct option.
Create at least three measures and use them in cards or similar visuals (not required for every
dashboard, but in total).
●
Use at least one of each of the following visuals, across all your pages. This is not for every page, but
taking all your pages together, utilize at least one of each type of the following visuals: timeline (line
or area), ribbon, bar, column, scatter (xy), filled map, treemap, histogram (use data grouping and a
column chart, not a custom visual), matrix, table, card (or multi-value card). Of course, you will use
some visual types more often than others. The above is a requirement to have at least one of those in
the list so you can experiment with the best chart(s) to show particular aspects.
●
On every dashboard, use slicers (you will need several slicers for some pages) and/or page-level
filters. Visual-level filters get added automatically for each visual for you to be able to tweak an
individual visual and you may need to customize settings on some individual visuals. For this
assignment, do not use the "Filters on all pages" capability (this affects all pages in the document, sort
of like filtering out rows in Transform Data would do).
Use the country code and country name intelligently. Do not assume the user will know the country
codes.
Submit
●
All pages must be interactive, that is, clicking on one visual automatically cross-filters/highlights the
others, and similarly for slicers and filters.
PBIX file
PDF file obtained by exporting the PBI file to a PDF file (File...Export).
Data Preparation Guidance
Change the names of the queries to Summer, Winter, and Countries. The default names are probably
inherited from the rather long file identifier in the links provided earlier in this document.
First row as headers. When importing text (e.g., CSV) files, the first row may not be automatically used
for column headers. In Home tab of Query Editor, Use First Row As Headers option is used to promote
the first row as headers.
Winter and Summer Files (do these steps on each file)
O Delete the "Changed Type" step that PBI likely adds automatically. With this data, PBI's default
choices, result in errors for some of the columns (PBI looks at the first 200 rows to decide on
data types, and this causes issues for at least one file here). Deleting this step and reassigning
the appropriate data type (later) works better.
Olympics Data Preparation and Dashboard
Page 3 of 6 ●
●
Create New column named Season. Set to "Summer" for summer table; "Winter" for winter
table. The formula for this column is just = "Summer" or = "Winter", respectively when you are
in the Add Column dialog box. Make sure you do this before appending the queries!
Append the queries.
O Appending queries combines two queries into one by combining the rows; this is why we
needed a Season field in the previous step.
In this stage you will combine winter and summer results. It is critical that you successfully
complete the previous steps before doing this one.
While in Query Editor ("Transform Data" in PBI), select the summer query.
Go to Home Tab → Append Queries → Append Queries as New
In the dialog box, the summer query should already be filled in one field. In the other field,
select the winter query, and hit OK.
Rename the resulting query to be "olympic_combined" and verify that you have all rows from
both summer and winter in the combined query.
O
O
O
O
Clean the data in the olympic_combined query
O
Age, Height, and Weight columns. Replace NA with blank (i.e., nothing; you can usually also
type null without quotation marks). Convert to decimal number. You can do this one column at
a time or select all three columns and do it at once. Query Editor will probably insert the null
value for the empty values which is OK (it uses null to tell you that there is truly nothing
entered, not even a space character). If you try to convert a column containing some text
values to numeric, you will get errors.
Year. Convert to whole number (don't try to convert to date; we're just interested in year and
season so whole number is sufficient).
Season. Convert to text.
O
Medal column. This column contains Gold, Silver, Bronze, or NA. In contrast to some other
columns, NA is not missing data here. It means the athlete did not earn a medal. For clearer
communication, replace this with something like "No Medal."
Add a Conditional Column called Medal_Index. The Medal column is text, but it is really ordinal
categorical data (i.e, categorical data with a natural order from best to worst). We need to be
able to display results in Gold, Silver, Bronze, and "No Medal." To do this, create an index
column based on the Medal column. Specifically, add a conditional column. Assign the value 1
to Gold, 2 to Silver, 3 to Bronze, and 4 to "No Medal" (see screen shot). After closing Query
Editor, we instruct Power BI to sort the Medal column based on the value of the Medal_Index
column (keep reading for instructions).
Close and Apply to return to Power BI
Model View. Create the connection between the "Olympic Code" column in the olympic_countries
table and the "Country" column in the olympic_combined table. Go to the Model Tab and create a 1-to-
many relationship between Olympic_Code in the olympic_countries table and Country in the
olympic_overall table.
Data View. In the Data View for the olympic_combined table, select the Medal column. Choose the
Column Tools tab, and "Sort by Column" dropdown. Sort the Medal column by the value of the
Medal_Index column. This does not immediately sort the Medal column. Rather, it tells Power BI that
whenever Medal is included in a visual, that the medal names will be listed in order according to the
Medal_Index column (you can choose ascending or descending). Check this when you create your first
visual or table listing the medal types and counts.
O
O
Olympics Data Preparation and Dashboard
Page 4 of 6 Conditional Column Creation for Model_Index
●
●
●
●
●
Add Conditional Column
●
Add a conditional column that is computed from the other columns or values.
New column name
Medal_Index
If
Else If
Else If
Else If
Else (0)
123
Column Name
Medal
Medal
Medal
Add Clause
Medal
null
●
Operator
equals
equals
equals
equals
Sport
Event
Year (of Olympics)
City (of Olympics)
Value >
123
123
Gold
Silver
123 Bronze
123 No Medal
Then
Data Dictionary
olympic_summer and olympic_winter files. Each row represents one entry into an event.
Athlete
Gender
PBIX/td-p/800887
Output
12-1
ABC
Then 123
Then 123
Then
2
3
4
ОК
Age (some data is missing; see Data Preparation section)
Height (cm) (some data is missing; see Data Preparation section)
Weight (kg) (some data is missing; see Data Preparation section)
Country abbreviation of athlete
Cancel
X
Medal (Gold, Silver, Bronze, or NA, where NA means athlete competed but did not win a medal. See
guidance in Data Preparation for how to deal with the NA values)
olympic_countries file
Country
Olympic Code (abbreviation of country, compatible with Country in other files)
Making Copy of Report Page, From One File to Another
You will likely be partly working independently on your own files, and then seeking to combine (PBI
Desktop does not have “live” sharing). This will help. You can copy one report page from one file to
another using the following steps:
Files must have the same data source(s). Sources here are the three CSV files.
Add a blank page in your target PBIX file
Go to your source PBIX file, click on the page you want to copy, and with nothing selected on the
report page hit CTRL + A (select all). Then hit CTRL + C (copy).
Go to your target report file and on the blank page, hit CTRL+V
See https://community.powerbi.com/t5/Desktop/Copy-report-page-from-PBIX-to-another-
Notes and Tips
PBI does not have a direct way to allow multiple people to edit the file simultaneously. You may want
to keep your PBIX in a cloud storage folder and make sure everyone has read/write access to that
folder, to better maintain version control. You will still need to update the Data Source settings when
opening the PBIX file on another computer.
Olympics Data Preparation and Dashboard
Page 5 of 6
