Exercise 1: Connect to Data

In this exercise, you will connect real public Crocodile Captures data in the Excel file, select the tables you want to use, and import them for transformation.  Public and organisational datasets will often require understanding and shaping in order to make for interesting and useful analytics.

Real NT Public Data

Skills in Power Query are essential in being able to take charge of the clean-up and perhaps re-organisation of your input datasets.   While you wait for the data owner to clean-up the data or provide it in another format, Power Query gives you the power to take this into your own hands and you will be surprised what you can do.

Preparation, shaping and cleansing is the strength of Power Query

1. First, Open up Power BI Desktop

2. Select “Get Data” from prompt

3. Select Excel

4. Navigate to your Day 1 starter materials

5. Open NT Crocodile Capture.xlsx

Notice how Power BI has detected 10 tables of data within the NT Crocodile Captures.xlsx file and has even detected extra datasets under Suggested Tables.

6. Check the box the following tables and select “Load”

• 2020 and Earlier Captures
• 2021 Captures
• 2022 Captures
• Crocs
• Zone

Notice how you can also preview the data in each sheet on the section on the right.

7. Note that the tables loaded underneath the Fields pane

8. Open up the Data Preview pane by clicking on the table on the far-left hand side. Look through each data table you loaded to familiarise yourself with the data. Look through the data can be enhanced by filtering using the chevrons at the top of each column.  This gives you a quick profile of the distinct lists of values that are present in your data.  Is there anything you would change?