Analyzing and Visualizing Data with Microsoft Excel v1.0

DRAG DROP -
You use a workbook query to import a table named Customers that contains a column named CustomerName. CustomerName has names in the format of
Lastname, Firstname.
You need the CustomerName column to contain names in the format of Firstname Lastname. A space must separate Firstname and Lastname.
Which two commands should you use? To answer, drag the appropriate commands to the correct areas. Each command may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point.
Select and Place:

You have two queries named Client and Invoices. A sample of Client is shown in the following table.

A sample of Invoices is shown in the following table.

You need to create a new table that has the following information.

Which join kind should you use?

You have a workbook query that loads the following table

You pivot the table on the Key column by using Value as the values column, and you receive the results shown in the following table.

You need to ensure that the data appears as shown in the following table.

What should you do?

You need to create a PivotChart that has a filter as shown in the following exhibit.

What should you do first?

DRAG DROP -
You create a new workbook and add a table to a data model. The data is shown in the following table.

You need to create a visualization as shown in the following exhibit.

Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
Select and Place:

You have the following table.

You plan to use [Month Name] as the axis in a PivotChart.
You need to ensure that whenever [Month Name] is used in a chart, the months are displayed chronologically by default.
What should you do?

You have the data model shown in the Data Model exhibit. (Click the Exhibit button.)
Data Model exhibit:

You have the PivotChart shown in the Pivot Chart exhibit. (Click the Exhibit button.)
Pivot Chart exhibit:

You need to change the current view of the PivotChart to display ProductCategory only.
What should you do?

DRAG DROP -
You have a model that contains the following table named Sales.

You have a measure named TotalSales that calculates the sum of LineTotal.
You plan to create a PivotChart to display TotalSales for each category and the percent of total sales for each category as shown in the following exhibit.

How should you complete the DAX formula for the Percent measure? To answer, drag the appropriate values to the correct areas. Each value may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point.
Select and Place:

You have a table in a Power Pivot model that is loaded from a Microsoft SQL Server database.
The source table has four columns named ID, Price, Quantity, and Total. Total is derived by multiplying Price and Quantity. ID is a unique row identifier.
You need to minimize the amount of memory used to load the model. The solution must ensure that you can create visualizations based on Price, Quantity, and
Total.
What should you do?

You add two tables named Date and Invoices to a data model. Invoices contains a column named InvoiceDate that has a data Type of Date. Date contains a column named DateID that has a Data Type of Whole Number. DateID is in the format of YYYYMMDD.
You need to create a relationship between Date and Invoices.
What should you do first?

HOTSPOT -
You have a table named Date that contains the following data.

You have a table named Sales that contains the following data.

You plan to create a PivotCharts that will be sliced by MonthName.
You need to create a relationship between Sales and Date.
Which columns should you use to create the relationship? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have two Microsoft SQL Server database servers named Production1 and Test1. Production1 contains the same tables as Test1, but only a subset of the data.
You add Test1 as a data source, and you select 10 tables. You configure several transformations.
You need to connect the model to the tables in Production1. The solution must maintain the existing transformations.
Solution: From Query Editor, you configure the Data source settings.
Does this meet the goal?

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have two Microsoft SQL Server database servers named Production1 and Test1. Production1 contains the same tables as Test1, but only a subset of the data.
You add Test1 as a data source, and you select 10 tables. You configure several transformations.
You need to connect the model to the tables in Production1. The solution must maintain the existing transformations.
Solution: From Query Editor, you edit the source of each table query.
Does this meet the goal?

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have two Microsoft SQL Server database servers named Production1 and Test1. Production1 contains the same tables as Test1, but only a subset of the data.
You add Test1 as a data source, and you select 10 tables. You configure several transformations.
You need to connect the model to the tables in Production1. The solution must maintain the existing transformations.
Solution: You create a new connection to Production1, and then you import the tables.
Does this meet the goal?