Question 1

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

Get & Transform (Power Query) feature allows you to create a dynamic connection to an existing Excel table and refresh the connection when the data in the table changes. This is done through the use of query parameters which can be set up to refresh the connection automatically. This ensures that any changes made to the data in the table are reflected in the query results.

Question 2

How can you rename a column in Excel?
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.

Accepted Answer

A

Question 3

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?
A) Warning Dialog box
B) Format Cells dialog box
C) Create Relationship Dialog box
D) Edit Relationship Dialog box

Accepted Answer

The Edit Relationship Dialog box is the only one mentioned in the options that allows the user to make changes to an existing relationship between Pivot tables.

Question 4

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?
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

Accepted Answer

DIn database management systems like Microsoft Access, editing a relationship between two tables is typically done under the "Design" tab, within the "Relationships" group. This is where users can manage and modify how tables relate to each other.

Question 5

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

Chris can remove rows with data errors by right-clicking on the selected columns and selecting Remove Errors.

Question 6

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?
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

Accepted Answer

In order to create a Scenario PivotTable report, we need to use the What-If Analysis tool, specifically the Scenario Manager. Therefore, the correct option is C, where X-What-If Analysis and Y-Scenario Manager. The other options are not relevant for creating a Scenario PivotTable report.

Question 7

To create a forecast sheet, enter two data series corresponding to each other in a worksheet, select both the data series, then click on the following: Data tab > Forecast group > Forecast Sheet > Create Forecast Worksheet box > Select Line Chart or Column Chart > Pick an end date in Forecast End box > Create.

Accepted Answer

This is the correct process to create a forecast sheet in Excel.

Question 8

Which of the following panes in the Power Map window contains a 3-D globe on which the data is mapped?
A) Layer Pane
B) Editor Pane
C) Center Pane
D) Tour Editor Pane

Accepted Answer

The Center Pane contains a 3-D globe on which the data is mapped in Power Map. The Layer Pane shows the layers that are currently visible in the map, the Editor Pane allows you to edit and format various aspects of the map, and the Tour Editor Pane is used to create and edit tours in Power Map.

Question 9

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?
A) Load To…
B) Duplicate
C) Reference
D) Move to Group

Accepted Answer

The "Load To…" option opens up a dialog box where you can choose to add the query to the worksheet or to the data model. Checking the "Add the data to the Data Model" box will ensure that the query data is added to the data model.

Question 10

To open the Manage Relationships dialog box in a Power View report, click the Design tab in the Power Pivot window, then click Manage Relationships in the Relationships group.

Accepted Answer

This is the correct method to open the Manage Relationships dialog box in a Power View report.

Question 11

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?
A) Tours group
B) Scene group
C) Tables group
D) Sparklines group

Accepted Answer

The correct option is A) Tours group. In Excel, to create a 3-D map scene in a tour, you would navigate through the Insert tab, then to the Tours group, where you can find the option for 3D Maps to open and create your 3D map scene.

Question 12

Vasalicki wants to create a query linked to a table. In which row should she enter field criteria in the query design grid?
A) Criteria row
B) Show row
C) Field row
D) Sort row

Accepted Answer

Field criteria should be entered in the Criteria row in the query design grid. This allows the user to filter the results of the query based on specific values or ranges in the selected field. The Show row is used to select which fields to display in the query results, the Field row is used to select the fields to include in the query, and the Sort row is used to specify the sorting order of the query results.

Question 13

Which of the following scene effects move in a repetitive circular motion?
A) Figure 8
B) Dolly
C) Rotate Globe
D) Push In

Accepted Answer

The answer of Which of the following scene effects move...

Question 14

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

This is the correct method of removing columns in Query Editor.

Question 15

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?
A) Inquire
B) Design
C) Power Pivot
D) Developer

Accepted Answer

The Table Name field is changed in the Design tab while adding a table to an internal Data Model in Excel. The Inquire tab is used for analyzing data and auditing worksheets, the Power Pivot tab is used for managing and analyzing data using the Power Pivot add-in, and the Developer tab is used for creating and editing macros and applications.

Question 16

In which of the following groups on the Database Tools tab will you click Relationships while creating a relationship between two queries?
A) Macro
B) Analyze
C) Move Data
D) Relationships

Accepted Answer

The Relationships group on the Database Tools tab is where you will find the option to create relationships between tables or queries in Access.

Question 17

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

This is the correct series of clicks and steps to use Microsoft Query to retrieve data from databases and files.

Question 18

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?
A) AutoFit
B) Pixel Space
C) Swap the X and Y axis
D) Lock current coordinate values

Accepted Answer

AutoFit will adjust the map to fit the available space and provide the best fit for the data. Pixel Space and Swap the X and Y axis are not relevant for adjusting the size and shape of the map, and Lock current coordinate values will prevent any further adjustments to the map's size and shape.

Question 19

Avery wants to transform data from data sources in order to analyze it. To do this, she can click Data > Get Data > Navigator window > Select table (or tables) to use in the query > Edit > OK.

Accepted Answer

The answer of Avery wants to transform data from data...

Question 20

What is the default value (in seconds) for a transition between scenes in a tour?
A) 1
B) 3
C) 10
D) 12

Accepted Answer

The default value for a transition between scenes is 3 seconds.

Quiz 10: Analyzing Data With Business Intelligence Tools

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.

How can you rename a column in Excel?

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?

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.

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?

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

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?

To open the Manage Relationships dialog box in a Power View report, click the Design tab in the Power Pivot window, then click Manage Relationships in the Relationships group.

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?

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

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

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.

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?

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

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?

Avery wants to transform data from data sources in order to analyze it. To do this, she can click Data > Get Data > Navigator window > Select table (or tables) to use in the query > Edit > OK.

What is the default value (in seconds) for a transition between scenes in a tour?