70-779 Dumps 2020-02

[Free] EnsurePass Microsoft 70-779 Real Exam Dumps Questions 1-10

February 7, 2020

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

Question No.1

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?

  1. Yes

  2. No

Correct Answer: B

Question No.2

You have a measure that is used by a KPI.

You need to display the output of the measure in a cell in your workbook. Which Excel function should you use?

  1. CUBEVALUE

  2. VLOOKUP

  3. CUBESET

  4. LOOKUP

Correct Answer: A

Explanation:

https://support.office.com/en-us/article/cubevalue-function-8733da24-26d1-4e34-9b3a- 84a8f00dcbe0

Using Excel CUBE Functions with PowerPivot

Question No.3

Note: This question is part of a series of questions that use the same scenario, For your convenience 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.

image

The data model is shown in the Data Model exhibit.

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 are two measures defined in DailyRepairs. Total Hours sums the Hours column, and Total Revenue sums the Revenue column.

image

Workshops has 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 alt the repair types. Dates has a list of dates from 2015 to 2018.

End of repeated scenario.

You create a measure named Average Revenue Per Hour that calculates the average revenue per hour.

You need to populate a cell in a worksheet to display the Average Revenue Per Hour where Repair Type is Engine.

Which Excel formula should you use?

image

  1. Option A

  2. Option B

  3. Option C

  4. Option D

Correct Answer: D

Question No.4

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

image

A sample of Invoices is shown in the following table.

image

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

image

Which join kind should you use?

  1. Inner

  2. Left Outer

  3. Right Anti

  4. Left Anti

Correct Answer: B

Explanation:

https://www.excelguru.ca/blog/2015/12/16/merge-tables-using-outer-joins-in-power-query/

Question No.5

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.)

image

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.

image

Which type of chart should you use?

  1. Line with markers

  2. clustered column

  3. stacked column

  4. combo

Correct Answer: D

Explanation:

https://support.office.com/en-us/article/available-chart-types-in-office-a6187218-807e-4103-9e0a- 27cdb19afb90

Question No.6

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 which Number. DateID is in the format of YYYYMMDD.

You need to create a relationship between Date and Invoices. What should you do first?

  1. Change the Data Type of InvoiceDate and DateID to Text.

  2. Create a measure in Invoices that uses the Format DAX Function.

  3. Change the Data Type of DateID to Date.

  4. Create a calculated column in Invoices that uses the Format DAX function.

Correct Answer: C

Explanation:

https://support.office.com/en-us/article/data-types-in-data-models-e2388f62-6122-4e2b-bcad- 053e3da9ba90?ui=en-USamp;rs=en-USamp;ad=US# toc319430522

Question No.7

DRAG DROP

Note: This question is part of a series of questions that use the same scenario, For your convenience 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 Data Model exhibit.

image

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.

image

Which field should you use for each area? To answer, drag the appropriate fields to the correct areas. Each field 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.8

You have an Excel workbook query that loads data to a worksheet and the data model. You need to ensure that the data is refreshed whenever you open the workbook.

What should you do?

  1. From the File tab, click Option, and then modify the Data option.

  2. From the File tab, click Options, and then modify the General options.

  3. From the Data tab, click Queries amp; Connections, and then edit the properties of the query.

  4. From the Power Pivot model, modify the Table Behavior setting.

Correct Answer: C

Explanation:

https://support.office.com/en-us/article/refresh-an-external-data-connection-in-excel-2016-for- windows-1524175f-777a-48fc-8fc7-c8514b984440

Question No.9

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 measure that uses the USERELATIONSHIP DAX function. Does this meet the goal?

  1. Yes

  2. No

Correct Answer: B

Question No.10

DRAG DROP

You have a workbook query that gets data from a table in a Microsoft Azure SQL database. The table has a column named Phone. The values in Phone are in a format of 999-999-9999. The first three digits of each phone number represent the area code, and the rest of the digits represent the local phone number.

You need to split the Phone column into two columns. The first column must contain the area code, and the second column must contain the local phone number.

How should you complete the query? To answer, drag the appropriate values 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

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