Question 1

Rachel wants to use Microsoft Query to retrieve data from her corporate databases and files so that she doesn't have to retype the data that she wants to analyze in Excel. To do so, she can click and/or enter the following information in the following series of clicks: Data tab > Get External Data group > From Other Sources > From Microsoft Query > Databases tab (To specify a data source for a database, text file, or Excel workbook) > <New Data Source> OK > Create New Data Source dialog box > Type a name to identify the data source > Click a driver for the type of database for use as data source > Connect (Provide the information needed to connect to the data source) > OK.

Accepted Answer

A) True 
 B)False  True

Question 2

Which of these will you click select as X in this series of clicks/entries/selections to edit relationships between Pivot tables: Power Pivot window > Design tab > Manage Relationships command > Manage Relationships dialog box > Relationship > Edit > X > Use the drop-down and list box controls on this form to select the appropriate table and field names?

Accepted Answer

A)   Warning Dialog box
B)   Format Cells dialog box
C)   Create Relationship Dialog box
D)   Edit Relationship Dialog box D

Question 3

In which of the following tabs will you change the Table Name field in the Properties group while adding a table to a internal Data Model?

Accepted Answer

A)   Inquire
B)   Design
C)   Power Pivot
D)   Developer B

Question 4

To open the Power Pivot window, one should click Power Pivot > Manage.

Accepted Answer

A) True 
 B)False

Question 5

Jacob wants to import a table from an Access database and put it into a workbook to organize it for a presentation. To do so, he opens a new workbook in Excel, clicks on the Data tab, then clicks on X under Get External Data. He then browses to the location of the database he wants to import and selects the table. He then selects the appearance of the data in the workbook, chooses the cell where he wants it to start, and finally clicks OK. What can X in the series of clicks be?

Accepted Answer

A)   From Text
B)   From Web
C)   Form Access
D)   From Other Sources

Question 6

To create a relationship between two tables, the first step would be to click on Relationships in the Relationships group on the Database Tools tab.

Accepted Answer

A) True 
 B)False

Question 7

Derek wants to remove columns from Query he doesn't need. To do so, he can select the columns he wants to remove with Ctrl+Click. Then he can select Remove Columns > Remove Columns in the Query Editor ribbon.

Accepted Answer

A) True 
 B)False

Question 8

Which of the following panes in the Power Map window contains a 3-D globe on which the data is mapped?

Accepted Answer

A)   Layer Pane
B)   Editor Pane
C)   Center Pane
D)   Tour Editor Pane

Question 9

Willa wants to insert a 3-D Map to explore data. Which of the following options should she click in Custom Maps Options box to get the best fit?

Accepted Answer

A)   AutoFit
B)   Pixel Space
C)   Swap the X and Y axis
D)   Lock current coordinate values

Question 10

Vasalicki wants to create a query linked to a table. In which row should she enter field criteria in the query design grid?

Accepted Answer

A)   Criteria row
B)   Show row
C)   Field row
D)   Sort row

Question 11

Edward wants to edit a relationship between two tables. To do this, he places the cursor pointing to the relationship line and then clicks the line to select it. Then, he clicks on tab "X" > Group "Y" > Edit Relationships. What are X and Y in the procedure?

Accepted Answer

A)   A-Design tab, B-Tools group
B)   A-Developer tab, B-Code group
C)   A-Format tab, B-Arrange group
D)   A-Design tab, B-Relationships group

Question 12

Which of the following will you click on in the Workbook Queries pane to check the "Add the data to the Data Model" box while adding a query to a Data Model?

Accepted Answer

A)   Load To…
B)   Duplicate
C)   Reference
D)   Move to Group

Question 13

Which of the following can X be in the series of clicks to create a scenario PivotTable report: Data tab > Data Tools > X > Y > Summary button > Scenario Summary dialog box > Report type > Scenario PivotTable report > Enter cell name in Results cells box > OK?

Accepted Answer

A)   X-Consolidate, Y-Consolidate
B)   X-What-If Analysis, Y-Goal Seek
C)   X-What-If Analysis, Y-Scenario Manager
D)   X-Data Validation, Y-Data Validation

Question 14

How can you rename a column in Excel?

Accepted Answer

A)   Double-click on the column in the Query Editor and enter a new name.
B)   Right-click on the column in the Query Editor and enter a new name.
C)   Left-click on the column in the Query Editor and enter a new name.
D)   Right-click on the column in the Query Editor, select Edit enter a new name.

Question 15

One can use Get & Transform (Power Query) feature of Excel to create a dynamic connection to an existing Excel table and refresh the connection when the data in table changes.

Accepted Answer

A) True 
 B)False

Question 16

Chris wants to remove the rows with data errors in a query. To do so, he can right-click on the selected columns from where he wants to remove the rows containing the errors and select Remove Errors.

Accepted Answer

A) True 
 B)False

Question 17

In which of the following groups on the Database Tools tab will you click Relationships while creating a relationship between two queries?

Accepted Answer

A)   Macro
B)   Analyze
C)   Move Data
D)   Relationships

Question 18

In which of the following boxes under Home > Scene Options will you insert the name for the scene name of a tour?

Accepted Answer

A)   Effect
B)   Scene Name
C)   Scene duration (sec) 
D)   Transition duration (sec)

Question 19

Which of the following scene effects move in a repetitive circular motion?

Accepted Answer

A)   Figure 8
B)   Dolly
C)   Rotate Globe
D)   Push In

Question 20

Finley wants to create a 3-D map scene in a tour. To do this, she selects a range of cells containing her data in an Excel table. She then clicks on Insert tab > X > 3D Maps > Open 3D Maps. What is X in the procedure?

Accepted Answer

A)   Tours group
B)   Scene group
C)   Tables group
D)   Sparklines group

Exam 10: Analyzing Data With Business Intelligence Tools

In which of the following tabs will you change the Table Name field in the Properties group while adding a table to a internal Data Model?

To open the Power Pivot window, one should click Power Pivot > Manage.

To create a relationship between two tables, the first step would be to click on Relationships in the Relationships group on the Database Tools tab.

Derek wants to remove columns from Query he doesn't need. To do so, he can select the columns he wants to remove with Ctrl+Click. Then he can select Remove Columns > Remove Columns in the Query Editor ribbon.

Which of the following panes in the Power Map window contains a 3-D globe on which the data is mapped?

Willa wants to insert a 3-D Map to explore data. Which of the following options should she click in Custom Maps Options box to get the best fit?

Vasalicki wants to create a query linked to a table. In which row should she enter field criteria in the query design grid?

Edward wants to edit a relationship between two tables. To do this, he places the cursor pointing to the relationship line and then clicks the line to select it. Then, he clicks on tab "X" > Group "Y" > Edit Relationships. What are X and Y in the procedure?

Which of the following will you click on in the Workbook Queries pane to check the "Add the data to the Data Model" box while adding a query to a Data Model?

Which of the following can X be in the series of clicks to create a scenario PivotTable report: Data tab > Data Tools > X > Y > Summary button > Scenario Summary dialog box > Report type > Scenario PivotTable report > Enter cell name in Results cells box > OK?

How can you rename a column in Excel?

One can use Get & Transform (Power Query) feature of Excel to create a dynamic connection to an existing Excel table and refresh the connection when the data in table changes.

Chris wants to remove the rows with data errors in a query. To do so, he can right-click on the selected columns from where he wants to remove the rows containing the errors and select Remove Errors.

In which of the following groups on the Database Tools tab will you click Relationships while creating a relationship between two queries?

In which of the following boxes under Home > Scene Options will you insert the name for the scene name of a tour?

Which of the following scene effects move in a repetitive circular motion?

Finley wants to create a 3-D map scene in a tour. To do this, she selects a range of cells containing her data in an Excel table. She then clicks on Insert tab > X > 3D Maps > Open 3D Maps. What is X in the procedure?