Up To Date 70-779 Preparation Exams 2020

we provide Verified Microsoft 70-779 practice which are the best for clearing 70-779 test, and to get certified by Microsoft Analyzing and Visualizing Data with Microsoft Excel (beta). The 70-779 Questions & Answers covers all the knowledge points of the real 70-779 exam. Crack your Microsoft 70-779 Exam with latest dumps, guaranteed!

Microsoft 70-779 Free Dumps Questions Online, Read and Test Now.

NEW QUESTION 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, 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.
70-779 dumps exhibit
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.
70-779 dumps exhibit
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?

  • A. Yes
  • B. No

Answer: B

NEW QUESTION 2
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.。
70-779 dumps exhibit
70-779 dumps exhibit
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?

  • A. Yes
  • B. No

Answer: B

NEW QUESTION 3
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. Create a perspective that has only the ProductName field from DimProduct selected.
  • B. Select all the columns in DimProduct except ProductName, right-click the columns, and then click Hide from Client Tools.
  • C. Edit the Default Field Set for DimProduct and add ProductName to the Default Field.
  • D. Edit the Table Behavior settings for DimProduct and add ProductName to the Default Label.

Answer: B

Explanation:
https://support.office.com/en-us/article/hide-columns-and-tables-in-power-pivot-ddf5b1f2-2ed2-4bdb-8f78-6f94

NEW QUESTION 4
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 worksheet. The data model is shown in the Data Model exhibit. (Click the Exhibit button.)
Your company has 100 product subcategories and more than 10,000 products.
70-779 dumps exhibit
End of repeated scenario.
You need to create a measure named [Sales Monthly RT] that calculates a running total of [Sales] for each date within a month as shown in the following exhibit.
70-779 dumps exhibit
70-779 dumps exhibit

  • A. Mastered
  • B. Not Mastered

Answer: A

Explanation:
SUM([Sales],DATESMTD(DimDate[Date])) CALCULATE(SUM([Sales],DATESMTD(DimDate[Date])))

NEW QUESTION 5
You use a workbook query to import a table named Customers that contains a column named CustomerName. CustomerName has names in the format of Lastname, Firstname.
You need the CustomerName column to contain names in the format of Firstname Lastname. A space must separate Firstname and Lastname.
Which two commands should you use? 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.
70-779 dumps exhibit

  • A. Mastered
  • B. Not Mastered

Answer: A

Explanation:
70-779 dumps exhibit Split Column By Delimiter
70-779 dumps exhibit Merge Columns
https://support.office.com/en-us/article/split-a-column-of-text-power-query-5282d425-6dd 0-46ca-95bf-8e0da9539662
https://support.office.com/en-us/article/merge-columns-power-query-80ec9e1e-1eb6-4048-b500-d5d42d9f0

NEW QUESTION 6
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:
70-779 dumps exhibit
The data model is shown in the Data Model exhibit. (Click the Exhibit button.)
70-779 dumps exhibit
The tables in the model contain the following data:
70-779 dumps exhibit 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.
70-779 dumps exhibit 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.
70-779 dumps exhibit RepairTypes has a list of all the repair types
70-779 dumps exhibit 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?

  • A. =CUBEMEMBER(“ThisWorkbookDataModel”, “[DailyRepairs]. [Avg Revenue Per Hour]”,CUBEMEMBER (“ThisWorkbookDataModel”, “[Dimensions]. [Repair Type]. [Engine]”))
  • B. =CUBEVALUE(“ThisWorkbookDataModel”, “[Measures]. [Avg Revenue Per Hour]”, CUBEMEMBER (“ThisWorkbookDataModel”, “[Dimensions]. [Repair Type]. [Engine]”))
  • C. =CUBEMEMBER(“ThisWorkbookDataModel”, “[DailyRepairs]. [Avg Revenue Per Hour]”, CUBEMEMBER (“ThisWorkbookDataModel”, “[RepairTypes]. [Repair Type]. [Engine]”))
  • D. =CUBEVALUE(“ThisWorkbookDataModel”, “[Measures]. [Avg Revenue Per Hour]”, CUBEMEMBER (“ThisWorkbookDataModel”, “[RepairTypes]. [Repair Type]. [Engine]”))

Answer: B

Explanation:
References:
https://support.office.com/en-us/article/cubevalue-function-8733da24-26d1-4e34-9b3a-84a8f00dcbe0 https://www.tutorialspoint.com/advanced_excel_functions/advanced_excel_cube_cubemember_function.htm

NEW QUESTION 7
From a workbook query, you import a table that has the following data.
70-779 dumps exhibit
You need to configure the table to appear as shown in the following table.
70-779 dumps exhibit
What should you do?

  • A. From the Format menu, click Trim.
  • B. From the Format menu, click Clean.
  • C. From the Split Column menu, click By Delimiter.
  • D. From the Extract menu, click Last Characters.

Answer: A

NEW QUESTION 8
You have a workbook query that loads data from C:\Data\Users.xlsx. You move Users.xlsx to a shared folder on the network.
You need to ensure that you can refresh the data from Users.xlsx. What should you do?

  • A. From the Linked Table tab in Power Pivot, modify the Update Mode.
  • B. From Query Editor, modify the Source step.
  • C. From the Insert tab in Excel, click My Add-ins, and then manage the add-ins.
  • D. From the Data tab in Excel click Connections, and then modify the properties of the connection.

Answer: D

NEW QUESTION 9
You have a query as shown in the following exhibit.
70-779 dumps exhibit
You need to ensure that the data only contains rows that have a valid date.
Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
70-779 dumps exhibit

  • A. Mastered
  • B. Not Mastered

Answer: A

Explanation:
Change the column type of the Date Column Group rows by the Date column
Remove errors from the date column.

NEW QUESTION 10
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.
70-779 dumps exhibit
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?

  • A. Move the database to a Microsoft Azure SQL databas
  • B. Load the table to the data model.
  • C. Load the data to the data model as three tables named Clients, Orders, and Product
  • D. Ensure that each table has only the relevant column
  • E. Remove duplicate rows from Clients and Products.
  • F. Load the data to one worksheet.
  • G. Load the data to three worksheets named Clients, Orders, and Product
  • H. Ensure that each worksheet has only the relevant column
  • I. Remove duplicate rows from Clients and Products.

Answer: B

NEW QUESTION 11
You have a workbook query that loads the following table
70-779 dumps exhibit
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.
70-779 dumps exhibit
You need to ensure that the data appears as shown in the following table.
70-779 dumps exhibit
What should you do?

  • A. Change the aggregate value function of the pivot.
  • B. Select the ID column, and then click Unpivot Columns
  • C. Change the Data Type of the Value column.
  • D. Delete the Picoted Column ste
  • E. Select the Key column, and the click UnpivotColumns.

Answer: B

Explanation:
References:
https://support.office.com/en-us/article/unpivot-columns-power-query-0f7bad4b-9ea1-49c1-9d95-f588221c7098

NEW QUESTION 12
You have a model that contains data relating to corporate profits. The model contains a measure named Profit.
You need to create a PivotTable to display the Profit measure in three different formats by using the Show Value As feature. The PivotTable must produce the results shown in the following table.
70-779 dumps exhibit
How should you configure the Show Value As feature for % Profit of Annual Total and % profit of Grand Total? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
70-779 dumps exhibit

  • A. Mastered
  • B. Not Mastered

Answer: A

Explanation:
% Profit of Annual Total: % of Parent Total
% Profit of Grand Total: % of Column Total
https://support.office.com/en-us/article/show-different-calculations-in-pivottable-value-f ields-014d2777-baaf-480b-a32b-98431f48bfec

NEW QUESTION 13
You have an Excel workbook that contains a table named Sales. You add Sales to the Power Pivot model.
You need to set a column named TransactionID as the row identifier for the Sales table. What should you do?

  • A. From Power Pivot, modify the Table Behavior setting.
  • B. From Query Editor, add an index column.
  • C. From Query Editor, modify the Data Type.
  • D. From Power Pivot, modify the Default Field Set.

Answer: A

Explanation:
https://msdn.microsoft.com/en-us/library/hh560542(v=sql.110).aspx
70-779 dumps exhibit In the Data View of your PowerPivot Window, click the PowerPivot Window: Advanced Tab.
70-779 dumps exhibit Click the table tab at the bottom of the window to select the table for which you are configuring
properties.
70-779 dumps exhibit In Reporting Properties, click Table Behavior.
70-779 dumps exhibit Set the Row Identifier, and then proceed to specify other properties in this dialog.
Opening the Table Behavior dialog box https://ksdconsultancy.blog/2015/10/08/set-table-behaviour-in-powerpivot/

NEW QUESTION 14
You have the following tables in a data model.
70-779 dumps exhibit
You create a PivotTable to display SaleAmount by Month. A sample of the results are shown in the following table.
70-779 dumps exhibit
You need to ensure that the data appears in chronological order. What should you do?

  • A. In the data model, modify the Sort By Column setting 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 Sales[Date]
  • D. From PivotTable Fields, modify the Field Settings for Date[Month Year]

Answer: D

NEW QUESTION 15
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:
70-779 dumps 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.
70-779 dumps exhibit
Which type of chart should you use?

  • A. Line with markers
  • B. clustered column
  • C. stacked column
  • D. combo

Answer: D

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

NEW QUESTION 16
You have a table named Date that contains the following data.
70-779 dumps exhibit
You have a table named Sales that contains the following data.
70-779 dumps exhibit
You plan to create a PivotCharts that will be sliced by MonthName. You need to create a relationship between Sales and Date.
Which columns should you use to create the relationship? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
70-779 dumps exhibit

  • A. Mastered
  • B. Not Mastered

Answer: A

Explanation:

DATE: DateKey SALES: OrderDate Refer below
70-779 dumps exhibit
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

NEW QUESTION 17
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?

  • A. yes
  • B. No

Answer: B

NEW QUESTION 18
You have multiple workbook queries that load data from tables in Microsoft Azure SQL Database to a Power Pivot data model.
You discover that new rows were added to the tables in Azure SQL Database. You need to ensure that the workbook has the new data.
Why should you do?

  • A. Select a cell in the worksheet and pressF5.
  • B. From the data tab, click Refresh All.
  • C. Close and open the workbook.
  • D. From the Power Pivot tab, click Update All.

Answer: B

Explanation:
Refresh data from a Microsoft Query, the Data Connection Wizard, or web query
70-779 dumps exhibit Click any cell in the range or table that contains the link to the external data.
70-779 dumps exhibit On the Data tab, in the Connections group, click Refresh All.
70-779 dumps exhibit
70-779 dumps exhibit To update only the selected data, click Refresh.
You can also right-click a cell in the range or table, and then click Refresh.
70-779 dumps exhibit To update all the data connections in the workbook, click Refresh All.
Note: If you have more than one workbook open, you'll need to repeat the operation in each workbook. https://support.office.com/en-us/article/refresh-an-external-data-connection-in-excel-2016-for-windows-152417

NEW QUESTION 19
You create a PivotTable and a Pivot Chart as shown in the following exhibit.
70-779 dumps exhibit
You need to decrease the number of columns in the PivotChart to six. What should you do?

  • A. Right-click the PivotChart, click Format Axis, and then change Interval between marks to 2.
  • B. Right-click the PivotTable, click Group, and then change By from 10 to 20.
  • C. Right-click the PivotChart, click Select Data, and then remove five of the bins from the Horizontal (Category) Axis Labels.
  • D. Change the chart type to Histogram.

Answer: B

NEW QUESTION 20
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 an Excel workbook that contains a table named Table1. A sample of the data in Table1 is shown in the following table.
70-779 dumps exhibit
You need to create a PivotTable in PowerPivot as shown in the exhibit.
70-779 dumps exhibit
Solution: You create a hierarchy named Products that contains ProductCategory,
Solution: You create a measure named Products the uses the DataTable DAX Function. You add a PivotTable. You drag products to the Rows field. You drag Price to the Values field.
Does this meet the goal?

  • A. Yes
  • B. No

Answer: B

NEW QUESTION 21
......

P.S. Easily pass 70-779 Exam with 117 Q&As Exambible Dumps & pdf Version, Welcome to Download the Newest Exambible 70-779 Dumps: https://www.exambible.com/70-779-exam/ (117 New Questions)