Microsoft 70-779 Analyzing and Visualizing Data with Microsoft Excel Exam Practice Test

You import the data from two next files into a PowerPivot model to create two tables named Customers and Invoices. Each table contains a column named CustomerID.

When you attempt to create a relationship between the Customers table and the Invoices table by using the CustomerID column from each table, the relationship cannot be created due to duplicate CustomerID values.

You need to ensure that you can create the relationship.

What should you do?

You have a query that retrieves the following data.

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

What should you do?

You have a data model that has the following tables.

You create a PivotTable. The data displayed in the PivotTable is shown in the following table.

You need to ensure that the correct data is displayed.

What should you do?

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 the following data.

You need to retrieve a list of the unique ProductName entries.

Solution: Create a PivotTable that uses the ProductName field in the Rows area.

Does this meet the goal?

You have the following tables in a data model.

You create a PivotTable to display SaleAmount by Month. A sample of the results are shown in the following table.

You need to ensure that the data appears in chronological order.

What should you do?

You create an Excel workbook named SalesResults.xlsx. You create a workbook query that connects to a Microsoft SQL Server database and loads data to the data model. You create a PivotTable and a PivotChart.

You plan to share SalesResults.xlsx to several users outside of your organization.

You need to ensure that the users can see the PivotTable and the PivotChart when they open the file. The data in the model must be removed.

What should you do?

Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is the same in each question in this series.

Start of repeated scenario.

You are creating reports for a car repair company. You have four datasets in Excel spreadsheets. Four workbook queries load the datasets to a data model. A sample of the data is shown in the Data Sample exhibit. (Click the Exhibit button.)

Data Sample exhibit:

The data model is shown in the Data Model exhibit. (Click the Exhibit button.)

The tables in the model contain the following data:

DailyRepairs has a log of hours and revenue for each day, workshop, and repair type. Every day, a log entry is created for each workshop, even if no hours or revenue are recorded for that day. Total Hours and Total Revenue column.

Workshops have a list of all the workshops and the current and previous workshop managers. The format of the Workshop Manager column is always Firstname Lastname. A value of 1 in the IsLatest column indicates that the workshop manager listed in the record is the current workshop manager.

RepairTypes has a list of all the repair types

Dates has a list of dates from 2015 to 2018

End of repeated scenario.

You create the column chart shown in the Pivot Chart exhibit. (Click the Exhibit button.)

You need to ensure that the month axis is displayed chronologically.

What should you do?