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?
(Choose Two)
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?
(Choose Two)
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?
(Choose Two)
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?
(Choose Two)
a) HASONEFILTER
b) DISTINCT
c) HASONEVALUE
d) VALUES
The purpose of this Sample Question Set is to provide you with information about the Analyzing and Visualizing Data with Microsoft Excel exam. These sample questions will make you very familiar with both the type and the difficulty level of the questions on the 70-779 certification test. To get familiar with real exam environment, we suggest you try our Sample Microsoft Analyzing and Visualizing Data with Microsoft Excel Certification Practice Exam. This sample practice exam gives you the feeling of reality and is a clue to the questions asked in the actual Microsoft Certified Solutions Associate (MCSA) - BI Reporting certification exam.