Examfast shares online exam exercise questions all year round! Microsoft MCSA 70-779 exam “Analyzing and Visualizing Data with Microsoft Excel”
https://www.leads4pass.com/70-779.html (109 Q&As).Continue to study and we provide an updated cisco 70-779 exam practice questions and answers.
You can practice the test online!

Table of Contents:

Latest Microsoft MCSA 70-779 pdf

[PDF] Free Microsoft MCSA 70-779 pdf dumps download from Google Drive: https://drive.google.com/open?id=1rKYauOFwJ2UkXI0L6DoAlcKkh3HrgCP5

Exam 70-779: Analyzing and Visualizing Data with Microsoft Excel:https://www.microsoft.com/en-us/learning/exam-70-779.aspx

Skills measured

This exam measures your ability to accomplish the technical tasks listed below.

  • Consume and Transform Data by Using Microsoft Excel (30-35%)
  • Model Data (35-40%)
  • Visualize Data (30-35%)

Who should take this exam?

Candidates for this exam should have a strong understanding of how to use Microsoft Excel to perform data analysis. Candidates should be able to consume, transform, model, and visualize data in Excel. Candidates should also be able to configure and manipulate data in PowerPivot, PivotTables, and PivotCharts. Candidates may include BI professionals, data analysts, and other roles responsible for analyzing data with Excel.

Microsoft MCSA 70-779 Exam Practice Questions

QUESTION 1
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. Transpose
B. Append Queries
C. Merge Queries
D. Combine Binaries
Correct Answer: B
Append is similar to UNION ALL in T-SQL. http://radacad.com/append-vs-merge-in-power-bi-and-power-query

QUESTION 2
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.

lead4pass 70-779 exam question q2

You need to ensure that the PivotTable appears in three columns as shown in the following exhibit.

lead4pass 70-779 exam question q2-1

A. On the Design tab, click Report Layout and then click Show in Compact Form.
B. Move Country from the Rows area to the Columns area.
C. Move Country from the Rows area to the Values area.
D. On the Design tab, click Report Layout and then click Show in Tabular Form.
Correct Answer: D

QUESTION 3
You have a table that contains data relating to exam candidates and their associated scores.
You need to visualize the exam data by separating the data into quartiles. The visualization must display the mean
score and must identify any outliers.
Which type of chart should you use?
A. line
B. histogram
C. pie
D. box and whisker
Correct Answer: D
https://support.office.com/en-us/article/create-a-box-and-whisker-chart-62f4219f-db4b-4754-aca8-4743f6190f0d

QUESTION 4
From a workbook query, you import a table that has the following data.lead4pass 70-779 exam question q4

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

lead4pass 70-779 exam question q4-1

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.
Correct Answer: C

QUESTION 5
You have a query that retrieves customers and their locations. You have sample of the data as shown in the following
table.

lead4pass 70-779 exam question q5

Additional customers and locations are added frequently.
You need to transform the data as shown in the following table.

lead4pass 70-779 exam question q5-1

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 6
You have an Excel workbook that contains two tables named User and Activity.
You plan to publish the workbook to the Power BI service.
Users will use QandA in the Power BI service to perform natural language queries.
You need to ensure that the users can query the term employee and receive results from the User table.
What should you do before you publish to Power BI?
A. From the Power Pivot model, edit the synonyms.
B. From PowerPivot Settings, modify the language options.
C. From PowerPivot Settings modify the categorization options.
D. From Workbook Connections, add a connection.
Correct Answer: A

QUESTION 7
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. Right-click the chart and then click Format Chart Area.
B. Right-click the chart and then click PivotChart Options.
C. On the Format tab, click Format Selection.
D. On the Design tab, click Change Chart Type.
Correct Answer: D

QUESTION 8
You have a table in a Microsoft SQL Server database that has more than 50 columns. A sample of the data and some of
the columns are shown in the following table.lead4pass 70-779 exam question q8

The table contains more than two million rows. You have 100 clients and 100 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 database. Load the table to the data model.
B. Load the data to the data model as three tables named Clients, Orders, and Products. Ensure that each table has
only the relevant columns. Remove duplicate rows from Clients and Products.
C. Load the data to one worksheet.
D. Load the data to three worksheets named Clients, Orders, and Products. Ensure that each worksheet has only the
relevant columns. Remove duplicate rows from Clients and Products.
Correct Answer: B

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. Pour workbook
queries load the datasets to a data model. A sample of the data is shown in the Data Sample exhibit.
Data Sample:lead4pass 70-779 exam question q9

The data model is shown in the Data Model exhibit. Data Mode

lead4pass 70-779 exam question q9-1

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 denned 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 FirstnameLastname. 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 create a measure named Average Revenue Per Hour that 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?

lead4pass 70-779 exam question q9-2

A. Option A
B. Option B
C. Option C
D. Option D
Correct Answer: C

QUESTION 10
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?
A. Microsoft Power BI Desktop
B. Excel and the Microsoft Power BI service
C. Excel only
D. the Microsoft Power BI service only
Correct Answer: A

QUESTION 11
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 12
You need to configure a PivotChart as shown in the following exhibit.lead4pass 70-779 exam question q12

Witch chart element should you enable?
A. Data Labels
B. Axis Titles
C. Data Table
D. Error Bars
Correct Answer: C

QUESTION 13
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. CUBEVALUE
B. VLOOKUP
C. CUBESET
D. LOOKUP
Correct Answer: A
https://support.office.com/en-us/article/cubevalue-function-8733da24-26d1-4e34-9b3a-84a8f00dcbe0
https://powerpivotpro.com/2010/06/using-excel-cube-functions-with-powerpivot/

QUESTION 14
You have a KPI named Goal that calculates the sales from the previous year and multiplies the sales by 1.1.
You need to modify Goal to multiply the sales from the previous year by 1.15.
What should you do?
A. From Power Pivot, modify the measure.
B. From the properties of the KPI, modify the KPI base field.
C. From Power Pivot, create a new calculated column, and then modify the KPI.
D. From the properties of the KPI, modify the absolute value.
Correct Answer: B

Related 70-779 Popular Exam resources

title pdf youtube Microsoft lead4pass Lead4Pass Total Questions
Microsoft MCSA lead4pass 70-779 dumps pdf lead4pass 70-779 youtube Analyzing and Visualizing Data with Microsoft Excel https://www.leads4pass.com/70-779.html 109 Q&A

Get Lead4Pass Coupons(12% OFF)

lead4pass coupon

What are the advantages of Lead4pass?

We have a number of Cisco, Microsoft, IBM, CompTIA, and other exam experts. We update exam data throughout the year. Top exam pass rate! We have a large user base. We are an industry leader! Choose Lead4Pass to pass the exam with ease!

about lead4pass

Summarize:

The free Microsoft MCSA 70-779 exam dumps can help you improve your skills and exam experience! To pass the cisco 70-779 exam at once: https://www.leads4pass.com/70-779.html We make Microsoft 70-779 videos and 70-779 pdf for you to learn! I hope you can pass the exam easily.