[Free] EnsurePass Microsoft 70-779 Real Exam Dumps Questions 31-40

Get Full Version of the Exam
http://www.EnsurePass.com/70-779.html

Question No.31

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 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 t]y the query

is shown in the following table.

image

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.

image

Solution: You select the-UserName and Location columns, and then you click Remove Duplicates. Does this meet the goal?

  1. Yes

  2. No

Correct Answer: A

Question No.32

From a workbook query, you import a table that has the following data.

image

You need to configure the table to appear as shown in the following table.

image

What should you do?

  1. From the Format menu, click Trim.

  2. From the Format menu, click Clean.

  3. From the Split Column menu, click By Delimiter.

  4. From the Extract menu, click Last Characters.

Correct Answer: A

Question No.33

You have the Excel worksheet shown in the exhibit.

image

You need to transform the data by using Query Editor. What should you do first?

  1. From the Data tab, Click From Table/Range.

  2. From the Insert tab, Click Store.

  3. From the Data tab, Click Flash Fill.

  4. From the Data tab, Click Consolidate.

Correct Answer: A

Explanation:

Excel uses a dedicated Query Editor to facilitate and display data transformations. When you select Data gt; Get Data , then select the data source, such as a workbook, or a database, the Navigator window appears so you can select which table (or tables) you want to use in your query. When you select a table, a preview of its data is shown in the right pane of the Navigator window.

https://support.office.com/en-us/article/getting-started-with-get-transform-in-excel-2016- a8310388-2a12-438c-9d29-c6d29cb8df6a?ui=en-USamp;rs=en-USamp;ad=US

Question No.34

Your company has a data analyst who uses Microsoft Power BI Desktop to create a data model and several reports.

The data analyst publishes the reports to the Power BI service.

You need to create a PivotTable in Excel that uses the data model created by the data analyst. The solution must prevent the data from being imported into Excel.

What should you do first?

  1. From powerbt.com, select the report. From the File menu, click Save as.

  2. From Excel, create a new query that uses the Data Catalog.

  3. From powerbi.com, select the report From the File menu, click Download report.

  4. From powerbi.com, select the report and click Analyze in Excel.

Correct Answer: D

Question No.35

You have the following table.

image

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 be default.

What should you do?

  1. Sort the [Month Name] column by [Month Name].

  2. Change the Data Type of [Month Name] to Date.

  3. Sort the [Month Name] column by [Month Name].

  4. Add a calculated column named [ID] that use the [Month Name] amp; [Month Number] DAX formula

Correct Answer: D

Explanation:

https://gasperkamensek.wordpress.com/2013/04/16/sorting-months-chronologically-and-not- alphabetically-in-a-pivot-table-report-based-on-power-pivot-data/

Question No.36

DRAG DROP

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:

image

image

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

image

The tables in the model contain the following data:

image

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.

image

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.

image

image

RepairTypes has a list of all the repair types Dates has a list of dates from 2015 to 2018

End of repeated scenario.

You need to create a PivotChart that displays the month, the hours of the month, and the hours of the previous month, as shown in the following exhibit.

image

Which DAX formula should you use for the Total Hours Last Month measure? To answer, drag the appropriate fields to the correct targets. 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.

image

Correct Answer:

image

Question No.37

You have an Excel workbook that has the following two workbook queries:

image

A query named consultants that retrieves a table named Consultants_Contact from a Microsoft SQL Server database

image

A query named employees that retrieves a table named Employee_Contact from a Microsoft Azure SQL database

Both tables have the same columns.

You need to combine all the data from Consultants and Employees into one table. Which command should you use?

  1. Transpose

  2. Append Queries

  3. Merge Queries

  4. Combine Binaries

Correct Answer: B

Explanation:

Append is similar to UNION ALL in T-SQL. http://radacad.com/append-vs-merge-in-power-bi-and-power-query

Question No.38

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 delete the existing queries, and then you add new data sources. Does this meet the goal?

  1. Yes

  2. No

Correct Answer: B

Question No.39

HOTSPOT

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:

image

image

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

image

The tables in the model contain the following data:

image

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.

image

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.

image

image

RepairTypes has a list of all the repair types Dates has a list of dates from 2015 to 2018

End of repeated scenario.

You need to add a custom column to the workbook query for Workshops that contains the email address of the workshop manager. The format of the email address is firstname.lastname@contoso.com.

How should you complete the query from Query Editor? To answer, select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

image

Correct Answer:

image

Question No.40

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.

image

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?

  1. Yes

  2. No

Correct Answer: A

Explanation:

https://www.mssqltips.com/sqlservertip/2900/creating-hierarchies-in-powerpivot-for-excel/ https://msdn.microsoft.com/en-us/library/ee634202.aspx

Get Full Version of the Exam
70-779 Dumps
70-779 VCE and PDF

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>