Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Exam Code: 70-779 (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Analyzing and Visualizing Data with Microsoft Excel (beta)
Certification Provider: Microsoft
Free Today! Guaranteed Training- Pass 70-779 Exam.
Check 70-779 free dumps before getting the full version:
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, 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?
Answer: B
NEW QUESTION 2
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 Data Model exhibit. (Click the Exhibit button.)
Exhibit:
Your company has 100 product subcategories and more than 10,000 products. End of repeated scenario.
You plan to use the DAX time intelligence functions of DATEADD and DATESMTD. You need to ensure that the functions return the correct data.
What should you do first?
Answer: C
Explanation:
https://docs.microsoft.com/en-us/sql/analysis-services/lesson-3-mark-as-date-table?view=sql-analysis-services-2
NEW QUESTION 3
You have the following table named SalesOrder Detail in a model.
You need to calculate the sum of SalesTotal for all the rows that have a quantity greater than 1.
Answer: A
Explanation:
CALCULATE(SUMX(‘SalesOrdersDetail’[SalesTotal]),‘SalesOrderDetail’[Quantity] > 1)
NEW QUESTION 4
You have the Excel worksheet shown in the exhibit.
You need to transform the data by using Query Editor. What should you do first?
Answer: A
NEW QUESTION 5
You have a workbook query that retrieves data from a table named Users. Users contains a column named PhoneNumber. The following is a sample of the data in PhoneNumber.
514 555 0169
1 (11) 500 555-0122
128 555-0148
819 555-0186
1-996-555-0192
+1 138-555-0156
556-555-0192
You need to create a custom column that contains the data in PhoneNumber in the format of 999-999-9999. The following is a sample of the desired data.
514-555-0160
500-555-0122
128-555-0148
819-555-0186
996-555-0192
138-555-0156
556-555-0192
How should you complete the Query Editor formula? 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.
Answer: A
Explanation:
Text.Replace(Text.End([Phone],12)," ","-")
NEW QUESTION 6
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.
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 7
You have the following table.
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?
Answer: D
Explanation:
References:
https://gasperkamensek.wordpress.com/2013/04/16/sorting-months-chronologically-and-not-alphabetically-in-a-
NEW QUESTION 8
You have a table that contains sales data.
You need to create a Pivot Table that will display the sales by country as shown in the following exhibit.
What should you use to display the icons?
Answer: B
NEW QUESTION 9
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:
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 2021
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.
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.
Answer: A
Explanation:
IF (ISBLANK[(Total Hours]),BLANK(), CALCULATE([total Hours], DATEADD(tblDates(Date),
-1,MONTH)))
NEW QUESTION 10
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?
Answer: A
NEW QUESTION 11
You have 12 sales reports stored in a folder as CSV files. Each report represents one month of sales data for a year. The reports have the same structure.
You need to analyze the entire year of sales data.
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.
Answer: A
Explanation:
Click From CSV, and then select the first file in the folder. Edit the query, and then click Append Queries.
From the Power Pivot tab, click Add to Data Model.
NEW QUESTION 12
You have a data model in Excel.
You export the data and the data model into a dataset in the Microsoft Power BI service. What can you use to modify the data model that is published to the Power BI service?
Answer: A
NEW QUESTION 13
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.
You need to create a new table that has the following information.
Which join kind should you use?
Answer: B
Explanation:
https://www.excelguru.ca/blog/2015/12/16/merge-tables-using-outer-joins-in-power-query/
NEW QUESTION 14
You have two visualizations as shown in the following exhibit.
Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic.
NOTE: Each correct selection is worth one point.
Answer: A
Explanation: 
NEW QUESTION 15
You merge several CSV files by using Query Editor.
You need to remove all the leading whitespaces and all the non-printable characters from a column.
What should you do to achieve each task? To answer, drag the appropriate actions to the correct goals. Each action 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.
Answer: A
Explanation:
Box 1: From the Extract menu, click Trim Box 2: From the Extract menu, click Clean
NEW QUESTION 16
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?
Answer: D
NEW QUESTION 17
You have a table named Sales. Sales contains the following columns.
You need to add a column that shows the first three letters of the day of the week.
How should you complete the DAX formula? 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.
Answer: A
Explanation:
FORMAT([Date], "DDD")
http://community.powerbi.com/t5/Desktop/Any-way-of-converting-numeric-month-amp-weeks-to-their/m-p/451
NEW QUESTION 18
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?
Answer: B
NEW QUESTION 19
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?
Answer: B
NEW QUESTION 20
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.
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 2021. 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?
Answer: D
NEW QUESTION 21
......
P.S. Easily pass 70-779 Exam with 117 Q&As Certifytools Dumps & pdf Version, Welcome to Download the Newest Certifytools 70-779 Dumps: https://www.certifytools.com/70-779-exam.html (117 New Questions)