The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
Vendor: Microsoft Exam Code: 70-779 Exam Name: Analyzing and Visualizing Data with Microsoft Excel Full Version Q & As: 118
Get FULL VERSION Real Exam Dumps http://www.ensurepass.com/70-779.html
Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
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, 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? A. Yes B. No Correct Answer: B QUESTION 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? A. B. C. D.
CUBEVALUE VLOOKUP CUBESET LOOKUP
Correct Answer: A Explanation: https://support.office.com/en-us/article/cubevalue-function-8733da24-26d1-4e34-9b3a84a8f00dcbe0 https://powerpivotpro.com/2010/06/using-excel-cube-functions-with-powerpivot/ QUESTION 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. Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
The data model is shown in the Data Model exhibit.
The tables in the model contain the following data: 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. 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. RepairTypes has a list of alt the repair types. Dates has a list of dates from 2015 to 2018. Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
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. B. C. D.
Option A Option B Option C Option D
Correct Answer: D QUESTION 4 You have two queries named Client and Invoices. A sample of Client is shown in the following table.
A sample of Invoices is shown in the following table.
Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
You need to create a new table that has the following information.
Which join kind should you use? A. B. C. D.
Inner Left Outer Right Anti Left Anti
Correct Answer: B Explanation: https://www.excelguru.ca/blog/2015/12/16/merge-tables-using-outer-joins-in-power-query/ QUESTION 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.)
Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
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.
Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
Which type of chart should you use? A. B. C. D.
Line with markers clustered column stacked column combo
Correct Answer: D Explanation: https://support.office.com/en-us/article/available-chart-types-in-office-a6187218-807e-4103-9e0a27cdb19afb90 QUESTION 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? A. B. C. D.
Change the Data Type of InvoiceDate and DateID to Text. Create a measure in Invoices that uses the Format DAX Function. Change the Data Type of DateID to Date. 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-bcad053e3da9ba90?ui=en-US&rs=en-US&ad=US#__toc319430522
QUESTION 7 Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
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.
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.
Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
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.
Correct Answer:
Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
QUESTION 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? A. B. C. D.
From the File tab, click Option, and then modify the Data option. From the File tab, click Options, and then modify the General options. From the Data tab, click Queries & Connections, and then edit the properties of the query. 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-forwindows-1524175f-777a-48fc-8fc7-c8514b984440 QUESTION 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.
Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
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 Correct Answer: B QUESTION 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.
Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
Correct Answer:
QUESTION 11 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. Your company has sales offices in several cities. You create a table that represents the amount of sales in each city by month as shown in the exhibit.
You need to ensure that all values lower than 250 display a red icon. The solution must ensure that all values greater than 500 display a green icon. Solution: You create a new conditional formatting rule that uses the Format only cells that contain rule type. Does this meet the goal? A. Yes B. No Correct Answer: A Explanation: https://www.exceltip.com/conditional-formatting/microsoft-excel-conditional-formatting.html QUESTION 12 Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
You have an Excel spreadsheet that contains a PivotChart. You install Microsoft Power BI Publisher for Excel. You need to add a tile for the PivotChart to a Power BI dashboard. What should you do? A. B. C. D.
From the Power BI tab in Excel, click Pin. From the File menu in Excel, click Publish. From powerbi.com, upload the excel workbook. From powerbi.com, click Get apps.
Correct Answer: A QUESTION 13 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. Your company has sales offices in several cities. You create a table that represents the amount of sales in each city by month as shown in the exhibit.
You need to ensure that all values lower than 250 display a red icon. The solution must ensure that all values greater than 500 display a green icon. Solution: From the What-If Analysis menu you modify the values of Goal Seek . Does this meet the goal? A. Yes B. No Correct Answer: B QUESTION 14 You have a measure named SalesGrowth that calculates the percent of sales growth. The Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
measure uses the following formula. ([Total Sales Current Year] - [Total Sales Last Year]) / [Total Sales Last Year] Total Sales Current Year is a measure that calculates the sales from the current calendar year. Total Sales Last Year is a measure that calculates the sales from the previous calendar year. You need to create a KPI that displays a red icon when the sales growth is less than last year. What should you use to define the target value? A. B. C. D.
an absolute value of 0 the Total Sales Current Year measure an absolute value of 100 the Total Sales Last Year measure
Correct Answer: D QUESTION 15 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 configure the Data source settings. Does this meet the goal? A. Yes B. No Correct Answer: A
QUESTION 16 You need to configure a PivotChart as shown in the following exhibit. Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
Witch chart element should you enable? A. B. C. D.
Data Labels Axis Titles Data Table Error Bars
Correct Answer: B
QUESTION 17 You have a query that retrieves the following data.
Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
You need to configure the query to ensure that the data appears as shown in the following table.
What should you do? A. B. C. D.
From the Transform tab, use the sum function on the Vendor_ID column Group by Vendor_ID and add a SUM aggregation Unpivot the table on the Vendor_ID column Pivot the table on the Vendor_ID column
Correct Answer: B QUESTION 18 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.
Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
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 Correct Answer: B QUESTION 19 You have the data model shown in the Data Model exhibit. (Click the Exhibit button.)
Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
You have the PivotChart shown in the Pivot Chart exhibit. (Click the Exhibit button.)
Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
You need to change the current view of the PivotChart to display ProductCategory only. What should you do? A. B. C. D.
Double-click a bar in the PivotChart. Click the - button. Right-click the PivotChart and click Reset to Match Style Right-click a bar in the PivotChart and click Expand Entire Field.
Correct Answer: D QUESTION 20 Your company has sales offices in several cities. You create a table that represents the amount of sales in each city by month as shown in the exhibit.
You need to ensure that all values lower than 250 display a red icon. The solution must ensure Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
that all values greater than 500 display a green icon. Solution: You create a measure, and then define a target value. Does this meet the goal? A. Yes B. No Correct Answer: B QUESTION 21 DRAG DROP You need to create a PivotChart as shown in the exhibit. (Click the Exhibit button.)
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.
Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
Correct Answer:
QUESTION 22 You have a workbook query that gets a table from an Excel workbook. The table contains a column1. In the query, you configure Column1 to use a Data Type of Whole Number. You refresh the data and find several errors in Column1. You discover that new entries in the table contain nonnumeric characters. You need to ensure that when the data is imported, any fields that contain nonnumeric values are set 1. What should you do from Query Editor? A. B. C. D.
Select the table and click Keep Errors. Select the column and click Replace Values... Select the column and click Remove Errors. Select the column and click Replace Errors...
Correct Answer: D QUESTION 23 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 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.)
Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
Your company has 100 product subcategories and more than 10,000 products. 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.
Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
Correct Answer:
Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
QUESTION 24 You have a PivotChart template named Template1. You add a PivotChart to a worksheet. You need to apply the template to the PivotChart. What should you do? A. B. C. D.
On the Design tab, click Change Chart Type. On the Format tab, click Format Selection. Right-click the chart and then click PivotChart Options. Right-click the chart and then click Format Chart Area.
Correct Answer: A Explanation: Click the chart. On the Charts tab, under Change Chart Type, click Other, and then under Templates, click the chart template that you created. https://stackoverflow.com/questions/17386777/how-to-apply-a-saved-chart-template-to-anexisting-chart QUESTION 25 HOTSPOT 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 PivotTabte must produce the results shown in the following table.
Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
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.
Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
Correct Answer:
QUESTION 26 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? A. Yes B. No Correct Answer: B
Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
QUESTION 27 DRAG DROP You have a model that contains the following table named Sales.
You have a measure named TotalSales that calculates the sum of LineTotal. You plan to create a PivotChart to display TotalSales for each category and the percent of total sales for each category as shown in the following exhibit.
How should you complete the DAX formula for the Percent measure? 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.
Correct Answer:
Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
QUESTION 28 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? A. B. C. D.
KPIs sparklines conditional formatting banded rows
Correct Answer: A
Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
QUESTION 29 DRAG DROP You create the PivotChart shown in the exhibit. (Click the Exhibit button.)
In which area is Product and in which area is SalesAmount? 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.
Correct Answer:
Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
QUESTION 30 You have a table named Sales that has three columns named Region, Country, and SalesAmount. You create a PivotTable as shown in the following exhibit.
You need to ensure that the PivotTable appears in three columns as shown in the following exhibit.
What should you do? A. B. C. D.
On the Design tab, click Report Layout and then click Show in Compact Form. Move Country from the Rows area to the Columns area. Move Country from the Rows area to the Values area. On the Design tab, click Report Layout and then click Show in Tabular Form.
Correct Answer: D QUESTION 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 Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
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 select the-UserName and Location columns, and then you click Remove Duplicates. Does this meet the goal? A. Yes B. No Correct Answer: A QUESTION 32 From a workbook query, you import a table that has the following data.
You need to configure the table to appear as shown in the following table.
Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
What should you do? A. B. C. D.
From the Format menu, click Trim. From the Format menu, click Clean. From the Split Column menu, click By Delimiter. From the Extract menu, click Last Characters.
Correct Answer: A QUESTION 33 You have the Excel worksheet shown in the exhibit.
You need to transform the data by using Query Editor. What should you do first? A. From the Data tab, Click From Table/Range. Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
B. From the Insert tab, Click Store. C. From the Data tab, Click Flash Fill. D. 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 > 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-2016a8310388-2a12-438c-9d29-c6d29cb8df6a?ui=en-US&rs=en-US&ad=US QUESTION 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? A. B. C. D.
From powerbt.com, select the report. From the File menu, click Save as. From Excel, create a new query that uses the Data Catalog. From powerbi.com, select the report From the File menu, click Download report. From powerbi.com, select the report and click Analyze in Excel.
Correct Answer: D QUESTION 35 You have the following table.
Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
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? A. B. C. D.
Sort the [Month Name] column by [Month Name]. Change the Data Type of [Month Name] to Date. Sort the [Month Name] column by [Month Name]. Add a calculated column named [ID] that use the [Month Name] & [Month Number] DAX formula
Correct Answer: D Explanation: Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
https://gasperkamensek.wordpress.com/2013/04/16/sorting-months-chronologically-and-notalphabetically-in-a-pivot-table-report-based-on-power-pivot-data/ QUESTION 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:
Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
The data model is shown in the Data Model exhibit. (Click the Exhibit button.)
The tables in the model contain the following data: 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. 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. 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.
Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
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.
Correct Answer:
Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
QUESTION 37 You have an Excel workbook that has the following two workbook queries: A query named consultants that retrieves a table named Consultants_Contact from a Microsoft SQL Server database 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? A. B. C. D.
Transpose Append Queries Merge Queries 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 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? A. Yes B. No Correct Answer: B
QUESTION 39 Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
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:
Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
The data model is shown in the Data Model exhibit. (Click the Exhibit button.)
The tables in the model contain the following data: 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. 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. 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.
Correct Answer:
Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
QUESTION 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.
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 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
Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
QUESTION 41 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 have six workbook queries that each extracts a table from a Microsoft Azure SQL database. The table 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. (Click the Exhibit button.)
Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
Your company has 100 products subcategories and more than 10,000 products. End of repeated scenario. You need to create a chart as shown in the following exhibit.
Which field should you use for each area? To answer, drag the appropriate field 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.
Correct Answer:
Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
QUESTION 42 HOTSPOT You have the following tables.
There is a relationship between the tables. You need to create a measure that displays how many users have a total TimeInMinutes that is greater than 60. How should you complete the DAX formula? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.
Correct Answer:
Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
QUESTION 43 DRAG DROP You have a query as shown in the following 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.
Correct Answer:
Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
QUESTION 44 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.
You need to create a PivotTable in PowerPivot as shown in the exhibit.
Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
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 Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
B. No Correct Answer: B QUESTION 45 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 table 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. (Click the Exhibit button.)
Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
Your company has 100 product subcategories and more than 10,000 products. End of repeated scenario. You have a PivotChart that uses Manufacturer as the axis and the sum of SalesAmount as the values. You need to ensure that only the top 10 manufactures appear in the chart. What should you do? A. B. C. D.
Change the format of the SalesAmount field. Create a calculated column. Configure the Value Filters. Summarize the SaleAmount field by Max.
Correct Answer: C Explanation: https://www.extendoffice.com/documents/excel/1963-excel-pivot-table-filter-top-10.html QUESTION 46 DRAG DROP 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.
Correct Answer:
Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
QUESTION 47 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.
You need to create a PivotTable in PowerPivot as shown in the exhibit.
Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
Solution: You create a hierarchy named Products that contains ProductCategory, ProductSubCategory, and ProductName. You add a PivotTable. You drag Products to the Rows field. You drag Price to the Values field. Does this meet the goal?
Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
A. Yes B. No Correct Answer: A Explanation: Hierarchy Products that contains ProductCategory, ProductSubCategory, and ProductName https://www.tutorialspoint.com/excel_power_pivot/excel_power_pivot_hierarchies.htm QUESTION 48 You have a query that retrieves customers and their locations. You have sample of the data as shown in the following table.
Additional customers and locations are added frequently. You need to transform the data as shown in the following table.
Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
What should you do? A. Select the Locations columns and the select split column by delimiter. Use a comma as the delimiter and split into rows. B. Select the Customer columns, and then click Unpivot Columns. C. Select the Customer columns, and then click Unpivot other Columns. D. Select the Location columns and then select split column by delimiter. Use a comma as the delimiter and split into columns. Correct Answer: A QUESTION 49 Your network contains a folder that has data files m various formats. You need to identify how many files of each extension type are in the folder by using Query Editor. What should you do? A. Create a query that uses a file source, and then use the Count Values command on the Transform tab. B. Create a query that uses a folder source, and then use the Group By command on the Home tab. C. Create a query that uses a file source, and then use the Group By command on the Home tab. D. Create a query that uses a folder source, and then use the Count Values command on the Transform tab. Correct Answer: B QUESTION 50 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.
Guaranteed Success with EnsurePass VCE Software & PDF File
The Latest 70-779 Exam ☆ Instant Download ☆ Free Update for 365 Days
You need to ensure that the data appears in chronological order. What should you do? A. B. C. D.
In the data model, modify the Sort By Column setting for Date[Month Year] From PivotTable Fields, add Date[Year] to the Rows area In the data model, modify the Sort by Column setting for Sales[Date] From PivotTable Fields, modify the Field Settings for Date[Month Year]
Correct Answer: D
Guaranteed Success with EnsurePass VCE Software & PDF File
EnsurePass.com All Products 20% OFF! Copy it : MMJ4-IGD8-X3QW
100-105 Dumps
200-105 Dumps
70-410 Dumps
663 Q&As
269 Q&As
517 Q&As
Updated: 02-01-2020
Updated: 02-01-2020
Updated: 02-01-2020
Price: $63.99
Price: $63.99
Price: $63.99
70-411 Dumps
70-412 Dumps
SY0-501 Dumps
399 Q&As
467 Q&As
659 Q&As
Updated: 02-01-2020
Updated: 02-01-2020
Updated: 02-01-2020
Price: $63.99
Price: $63.99
Price: $63.99
Hot Exams Dumps List 70-410
AZ-103
210-260
70-411
AZ-900
300-101
70-412
MD-100
300-115
70-461
MD-101
300-135
70-462
PL-900
300-206
70-463
300-208
70-480
100-105
300-209
70-483
200-105
300-210
70-486
200-125
300-320
70-487
200-150
400-101
70-740
200-155
70-741
200-310
200-1001
70-742
200-355
200-1002
70-761
210-060
SY0-501
70-762
210-065
CAS-003
70-767
210-250
70-768
210-255