Microsoft : 070-779 Questions & Answers

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?

• A. From Query Editor, open the Data Source Settings and delete the credentials.
• B. Run the Document Inspector.
• C. Save the workbook as an Excel Binary Workbook (.xlsb).
• D. Modify the source of the query.

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?

• A. From PivotTable Fields, modify the Field Settings for Date[Month Year]
• B. From PivotTable Fields, add Date[Year] to the Rows area
• C. In the data model, modify the Sort By Column setting for Date[Month Year]
• D. In the data model, modify the Sort by Column setting for Sales[Date]

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 a query named Query1 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 use the Group By function to group the rows by UserName and you specify output columns for UserID and Location by using the Max operation.
Does this meet the goal?

• A. Yes
• B. No

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 a Power Pivot model that contains the following tables.

There is a relationship between Products and ProductCategory.
You need to create a hierarchy in Products that contains ProductCategoryName and ProductName.
Solution: You create a calculated column that uses the RELATED DAX function Does this meet the goal?

• A. Yes
• B. No

You have a Power Pivot data model that contains a table named DimProduct DimProduct has seven columns named ProductKey, ProductLabel, ProductName, ProductDescription, ProductSubCategoryKey, Manufacturer, and Brand.
Only the members of the product team use all the data in the DimProduct table.
You need to simplify the model for other users by hiding all the columns except ProductName.
What should you do?

• A. Select all the columns in DimProduct except ProductName, right-click the columns, and then click Hide from Client Tools.
• B. Edit the Table Behavior settings for DimProduct and add ProductName to the Default Label.
• C. Create a perspective that has only the ProductName field from DimProduct selected.
• D. Edit the Default Field Set for DimProduct and add ProductName to the Default Field.