UO Business Intelligence
Data Clean and Connection
This guide is the second of the three instructions intended to help you to ‘clean’ and connect data
smoothly into either Power BI or Tableau for assignment dashboard.
It is uncommon, in the data analysis area, that collected data can be used and analysed straightaway
without a ‘cleaning process’. No matter what the format of the original data sources, it is necessary
to review and clean the data in a way that the nominated business intelligence tool will be able to
recognise and efficiently process. The following gives you some support on how to successful clean
your data.
1. Create your own Excel file
Create your own Excel file with the content duplicated from the raw data sources even if your raw
data is an Excel file. The raw data can be found in the different format: Excel, CSV, PDF, a table or a
graph on a website. Sometimes you cannot directly download the data easily with an Excel file so
that is why it is encouraged to have your own Excel file and manually duplicate the content from
the source into your Excel file. Meanwhile, don’t forget to write down the URL of the sources for
your references. The following are examples of useful data in different formats.
a. If your source is an Excel file, do not clean or rearrange the data on the original sheet
because you might need that later. Copy and paste the data that you are interested in into
your own separate Excel file. For example, you may want to copy only some columns and
rows from the most recent years.
1
b. If your source is information such as a table or a photo on a website, you should manually
copy and paste the data into your Excel file.
2. Put different sources on different sheets
It’s a good way to organise different sources on different sheets with the name properly assigned in
an Excel file. The benefit will be when later, if you are connecting tables.
2
3. Connect sheets/tables in business intelligence tools
It is not necessary to connect tables. Table connection takes place only if you have multiple sheets
in your Excel file. In other words, if you edit all your data into one sheet (a big data table) you don’t
have to connect tables simply because you don’t have another table.
If you use Power BI, the data tables will be connected automatically if they have the column in
common. For example, you have two sheets/tables 1 and 2 as follows:
If you input those two tables, they will be linked automatically by “Player”. You can find more detail
in week-4 https://uo.unisa.edu.au/mod/page/view.php?id=5060
If the relationship is not established, you will need to use the relationship panel to manually connect
tables as follows:
3
If you use Tableau, you can configure the join type for input tables, which is mentioned in week-5
practical: https://uo.unisa.edu.au/mod/book/view.php?id=11877&chapterid=2287
We still use the above two tables as an example to show 4 types you can configure in Tableau as
follows:
Inner join: only shows the common data in both tables (both players James and Tyler)
Full/outer join: it shows all data. (It connects all tables and present ‘null’ in the empty cell)
Last Edit: 18/6/2019 MK
