Introduction to Microsoft Certified Solutions Associate (MCSA) - BI Reporting Exam
The Microsoft Exam is challenging and thorough preparation is essential for success. This exam study guide is designed to help you prepare for the Analyzing and Visualizing Data with Microsoft Excel certification exam. It contains a detailed list of the topics covered on the Professional exam, as well as a detailed list of preparation resources. These study guide for the Microsoft Certified Solutions Associate (MCSA) - BI Reporting will help guide you through the study process for your certification.
70-779 Analyzing and Visualizing Data with Microsoft Excel Exam Summary
Exam Name: Microsoft Certified Solutions Associate (MCSA) - BI Reporting
Exam Code: 70-779
Exam Price: $165 (USD)
Duration: 120 mins
Number of Questions: 45-55
Passing Score: 700 / 1000
Reference Books: 20779A: Analyzing, Data with Excel
Schedule Exam: Pearson VUE
Sample Questions: Microsoft Analyzing and Visualizing Data with Microsoft Excel Sample Questions
Recommended Practice: Microsoft 70-779 Certification Practice Exam
Exam Syllabus: 70-779 Microsoft Certified Solutions Associate (MCSA) - BI Reporting
1. Consume and Transform Data by Using Microsoft Excel (30-35%)
Import from data sources: - Connect to and import from databases, files, and folders; connect to Microsoft SQL Azure, Big Data, SQL Server Analysis Services (SSAS), and Power Query; import supported file types; import from other Excel workbooks; link to data from other sources
Perform data transformations: - Design and implement basic and advanced transformations, apply business rules, change data format to support visualization, filter data, format data
Cleanse data: - Manage incomplete data; handle data received as a report; remove unnecessary rows, columns, and data; remove repeating headers
2. Model Data (35-40%)
Create and optimize data models: - Manage data relationships, optimize models for reporting, manually enter data, perform Get & Transform, create automatic relationships, create manual relationships
Create calculated columns, measures, and tables: - Create DAX queries, create DAX formulas, create Excel formulas
Create performance KPIs - Calculate the actual value, calculate the target value, calculate actual to target values
Create hierarchies: - Create date hierarchies, create business hierarchies, resolve hierarchy issues
3. Visualize Data (30-35%)
Create and manage PivotTables: - Format PivotTables and PivotCharts, format calculated measures, filter data, group and summarize data
Create and manage PivotCharts: - Select a chart type, format calculated measures, filter data; group and summarize data, use slicers
Interact with Power BI: - Import Excel data from Power BI, manipulate Excel data in Power BI
Microsoft 70-779Certification Sample Questions and Answers
To make you familiar with Analyzing and Visualizing Data with Microsoft Excel (70-779) certification exam structure, we have prepared this sample question set. We suggest you to try our Sample Questions for MCSA BI Reporting 70-779Certification to test your understanding of Microsoft 70-779process with real Microsoft certification exam environment.
70-779Analyzing and Visualizing Data with Microsoft Excel Sample Questions:-
01. You have a Data Model with one table in it, and data for a PivotTable you are building is sourced exclusively from the Data Model.
If the Data Model gets its data from a SQL Server via the Query Editor, what sequence of steps needs to happen to get refreshed into your PivotTable if you know that data at source has changed?
a) From the PivotTable Tools menu, click Refresh
b) From the PivotTable Tools menu, click Refresh All
c) From the Query Editor, click Home, Query, Refresh All
d) All the above
02. Which chart type is best to illustrate comparisons among individual items where there are many individual items?
a) Bar
b) Pie
c) Column
d) Waterfall
03. 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 Q&A 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 PowerPivot Settings, modify the Language options
b) From PowerPivot Settings, modify the Categorization options
c) From the Power Pivot model, edit the Synonyms
d) From Workbook Connections, add a connection
04. When creating a KPI, you need to set a Target value. What are the valid target values sources?
a) Explicit Measure
b) Excel Table
c) Absolute value
d) Implicit Measure
05. When you attempt to publish the file to Microsoft Power BI, you receive the following error message:
“We couldn’t publish to Power BI. Make sure your workbook is saved as an Excel file (.xlsx or .xlsm) and is not password protected.”
You need to ensure that you can publish the file to Power BI. What should you do first?
a) Decrypt the workbook
b) Copy the file to a network share
c) Add a digital signature to the workbook
d) Disable iterative calculation for the workbook
06. You have a table in a Power Pivot model that is loaded from a Microsoft SQL Server database. The source table has four columns named ID, Price, Quantity, and Total. Total is derived by multiplying Price and Quantity. ID is a unique row identifier.
You need to minimize the amount of memory used to load the model. The solution must ensure that you can create visualizations based on Price, Quantity, and Total. What should you do?
a) Replace the Total column by using a calculated column
b) Replace the Total column by using a measure
c) From Query Editor, remove duplicate rows from the table
d) Move the Total column to a lookup table
07. What is the result of the conversion when you write a calculated column by writing the following formula: = "8" + "7"?
a) 87
b) 15
c) “8+7”
d) An error message
08. You create an Excel workbook named SalesResults.xlsx. You create a workbook query that connects to a Microsoft SQL Server database and loads data to the data model. You create a PivotTable and a PivotChart.
You plan to share SalesResult.xlsx to several users outside of your organization. You need to ensure that the users can see the PivotTable and the PivotChart when they open the file. The data in the model must be removed.
What should you do?
a) Run the Document Inspector
b) Save the workbook as an Excel Binary Workbook (.xlsb)
c) From Query Editor, open the Data Source and delete the credentials
d) Modify the source of the query
09. Which two ways can be used to reduce the number of rows in a data set?
a) Filter Functions
b) Remove Columns
c) Parameters
d) Extract
10. Which functions in DAX return a one-column table that contains the distinct values from the specified table or column?
a) HASONEFILTER
b) DISTINCT
c) HASONEVALUE
d) VALUES
Answers:-
Answer 1 :- A
Answer 2 :- A, C
Answer 3 :- C
Answer 4 :- A, C
Answer 5 :- A
Answer 6 :- B
Answer 7 :- B
Answer 8 :- D
Answer 9 :- A, C
Answer 10:- B, D
Comentários