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

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?

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, white 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 a query named Quety1 that retrieves the user information from two Excel files. One of the Excel files does not contain location information. A sample of the data retrieved by the query is shown in the following table.

You need to ensure that values in UserName are unique. The solution must ensure that the locations are retained. A sample of desired output is shown in the following table.

Solution: You sort the UserName column in ascending order. You select the UserName column, and then you click Remove Duplicates. 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 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 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?

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 have six workbook queries that each extracts a table from a Microsoft Azure SQL database. The tables are loaded to the data model, but the data is not loaded to any worksheets. The data model is shown in the exhibit. (Click the Exhibit button.)

Exhibit:

Your company has 100 product subcategories and more than 10,000 products.

End of repeated scenario.

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

Which type of chart should you use?

You have a table named Sales that has three columns named OrderDate, OrderNumber, and SalesAmount.

You need to create the PivotTable as shown in the following table.

What should you use?

You have a table in a Microsoft SQL Server database that has more than 5 columns. A sample of the data and some of the columns are shown in the following table.

The table contains more than two million rows. You have 100 clients and 10 products.

You need to load the data to Excel. The solution must minimize the amount of memory used by the model.

What should you do?