Excel Power Tools for Data Analysis
by Macquarie University
Test your skills: Weeks 1 and 2
Question 1
Power Query (Get and Transform) allows us to get data from a wide variety of sources, which of
the following sources can we get data from using Power Query? (Multiple answers may apply.)
point
Excel
Web
Folder
Text Files
Correct
Yes, this is correct.
2.Question 2
Download the attached file and unzip it, then answer the questions below.
C3W2 Assessment
ZIP File
Open the Excel workbook Staff Expense Report and import the Staff table from the Staff.accdb
access database file into the Staff sheet. What is the ID for Jane Freeman?
point
113
114
115
116
Correct
Yes, this is correct.
3.Question 3
Have a look at the file Sydney Expenses. There are 3 worksheets, one for each of the staff you
manage. You need to bring this data into your report. In Staff Expenses Report create a query
to get the 3 sheets of data from Sydney Expenses. We are not ready to transform the data yet so
save the three queries without loading the data into the spreadsheet. Which option did you
select?
point
Load To and then Table
Transform
Load To and then Connection Only
Load
Correct
Yes, this is correct.
, Excel Power Tools for Data Analysis
4.Question 4
Create a new query called Expenses that combines the data from the 3 queries you created
previously, so the rows from each query sit one on top of the other ready to be transformed for
analysis. Which combine query type do you need to use?
point
Merge with a Left Outer Join
Append
Merge with Inner Join (matching items only)
Stack
Correct
Yes, this is correct.
5.Question 5
In the Expenses query, column 4 contains the Staff ID for each expense sheet shown above the
expense data. Transform the data so that the correct IDs repeat for every row of the expenses.
Which option on the Transform tab achieves this?
point
Fill Down
Rename
Fill Up
Replace Values
Correct
Yes, this is correct.
6.Question 6
In the Expenses query have a look at the heading row, it does not contain the correct headings,
the correct headings are in row 5. Which of the following sets of steps will get the values in row 5
to be our headers? (More than one option may apply.)
point
Remove the top 4 Rows, then select Use Headers as First Row
Remove the top 4 Rows, then select Use First Row as Headers
Correct
Yes, this is correct.
Select Use Headers as First Row, remove the top 5 Rows, then select Use First Row as
Headers
Correct
Yes, this is correct.
, Excel Power Tools for Data Analysis
Select Use First Row as Headers, remove the top 5 Rows, then select Use First Row as
Headers
7.Question 7
Still in the Expenses query, we need to remove the first and last columns. Select the first
column, hold down the Ctrl key and select the last column. Which of the following options can
you use to remove the selected columns? (More than one option may apply.)
point
Home Ribbon > Remove Other Columns
Home Ribbon > Remove Columns
Correct
Yes, this is correct.
Right Click and from the menu select Remove Other Columns
Press Delete on your keyboard
Correct
Yes, this is correct.
8.Question 8
Some of the column headers do not have very meaningful names, change the column headings
TRANSPORTATION to EXPENSE TYPE and 112 to STAFF ID. Which option on the ribbon
allows us to change a column name?
point
Manage
Fill
Rename
Replace Values
Correct
Yes, this is correct.
9.Question 9
There are still some rows that contain heading and total information that need to be removed.
Add a filter to the Code column to remove all rows that do not contain an Expense Code (scroll
down to ensure you have them all). How many items did you need to f ilter out (unselect)?
point
1
2
4
5
Correct
by Macquarie University
Test your skills: Weeks 1 and 2
Question 1
Power Query (Get and Transform) allows us to get data from a wide variety of sources, which of
the following sources can we get data from using Power Query? (Multiple answers may apply.)
point
Excel
Web
Folder
Text Files
Correct
Yes, this is correct.
2.Question 2
Download the attached file and unzip it, then answer the questions below.
C3W2 Assessment
ZIP File
Open the Excel workbook Staff Expense Report and import the Staff table from the Staff.accdb
access database file into the Staff sheet. What is the ID for Jane Freeman?
point
113
114
115
116
Correct
Yes, this is correct.
3.Question 3
Have a look at the file Sydney Expenses. There are 3 worksheets, one for each of the staff you
manage. You need to bring this data into your report. In Staff Expenses Report create a query
to get the 3 sheets of data from Sydney Expenses. We are not ready to transform the data yet so
save the three queries without loading the data into the spreadsheet. Which option did you
select?
point
Load To and then Table
Transform
Load To and then Connection Only
Load
Correct
Yes, this is correct.
, Excel Power Tools for Data Analysis
4.Question 4
Create a new query called Expenses that combines the data from the 3 queries you created
previously, so the rows from each query sit one on top of the other ready to be transformed for
analysis. Which combine query type do you need to use?
point
Merge with a Left Outer Join
Append
Merge with Inner Join (matching items only)
Stack
Correct
Yes, this is correct.
5.Question 5
In the Expenses query, column 4 contains the Staff ID for each expense sheet shown above the
expense data. Transform the data so that the correct IDs repeat for every row of the expenses.
Which option on the Transform tab achieves this?
point
Fill Down
Rename
Fill Up
Replace Values
Correct
Yes, this is correct.
6.Question 6
In the Expenses query have a look at the heading row, it does not contain the correct headings,
the correct headings are in row 5. Which of the following sets of steps will get the values in row 5
to be our headers? (More than one option may apply.)
point
Remove the top 4 Rows, then select Use Headers as First Row
Remove the top 4 Rows, then select Use First Row as Headers
Correct
Yes, this is correct.
Select Use Headers as First Row, remove the top 5 Rows, then select Use First Row as
Headers
Correct
Yes, this is correct.
, Excel Power Tools for Data Analysis
Select Use First Row as Headers, remove the top 5 Rows, then select Use First Row as
Headers
7.Question 7
Still in the Expenses query, we need to remove the first and last columns. Select the first
column, hold down the Ctrl key and select the last column. Which of the following options can
you use to remove the selected columns? (More than one option may apply.)
point
Home Ribbon > Remove Other Columns
Home Ribbon > Remove Columns
Correct
Yes, this is correct.
Right Click and from the menu select Remove Other Columns
Press Delete on your keyboard
Correct
Yes, this is correct.
8.Question 8
Some of the column headers do not have very meaningful names, change the column headings
TRANSPORTATION to EXPENSE TYPE and 112 to STAFF ID. Which option on the ribbon
allows us to change a column name?
point
Manage
Fill
Rename
Replace Values
Correct
Yes, this is correct.
9.Question 9
There are still some rows that contain heading and total information that need to be removed.
Add a filter to the Code column to remove all rows that do not contain an Expense Code (scroll
down to ensure you have them all). How many items did you need to f ilter out (unselect)?
point
1
2
4
5
Correct
