Analyzing and Visualizing Data with Microsoft Excel 70-779 Exam with Guarantee

1
IT Certification leaders in simulated test
engines guides
Fravo
Get Certified Secure your Future
Analyzing and Visualizing Data with Microsoft
Excel (beta) Exam 70-779 Demo Edition
2
QUESTION 1 HOTSPOT You have a table named Date
that contains the following data.
You plan to create Pivot Charts that will be
sliced by MonthName. You need to create a
relationship between Sales and Date. Which
Columns should you use to create the
relationship? To answer, select the appropriate
options in the answer area. NOTE Each correct
selection is worth one point.
3
Answer Exhibit
Explanation DATE DateKey SALES OrderDate
Refer below
https//www.archerpoint.com/blog/Posts/creating-da
te-table-power-bi
4

• QUESTION 2
• You have a Power Pivot data model that contains a
  table named DimProduct DimProduct has seven
  columns named ProductKey, ProductLabel,
  ProductName, ProductDescription,
  ProductSubCategoryKey, Manufacturer, and Brand.
  Only the members of the product team use all the
  data in the DimProduct table.
• You need to simplify the model for other users by
  hiding all the columns except ProductName. What
  should you do?
• Create a perspective that has only the
  ProductName field from DimProduct selected.
• Select all the columns in DimProduct except
  ProductName, right-click the columns, and then
  click Hide from Client Tools.
• Edit the Default Field Set for DimProduct and add
  ProductName to the Default Field.
• Edit the Table Behavior settings for DimProduct
  and add ProductName to the Default Label.
• Answer B
• Explanation
• https//support.office.com/en-us/article/hide-colu
  mns-and-tables-in-power-pivot-
  ddf5b1f2-2ed2-4bdb-8f78- 6f94503ca87a
• QUESTION 3

5

• QUESTION 4
• You have a workbook query that loads data from a
  table in a Microsoft Azure SQL database. The
  table has a column named LineTotal. The following
  is a sample of the data in LineTotal
• 40
• 1
• 999
• 7658
• 883432
• You need to ensure that when you load the data to
  the model, LineTotal is set as currency. What
  should you do from Query Editor?
• Configure the Data Type
• Round the column.
• Split the column by characters.
• Split the column by delimiter.
• Answer A

6
Merge is similar to JOIN in T-SQL http//radacad.c
om/append-vs-merge-in-power-bi-and-power-query Q
UESTION 6 HOTSPOT You have a workbook query
that uses an Excel data source. The data source
contains the following table.
You need the data to appear as shown in the
following table.
7
How should you transform the data from Query
Editor? To answer, select the appropriate
options in the answer area. NOTE Each correct
selection is worth one point.
Answer Exhibit
Explanation Columns to Select TestAScore
TestBScore TestCScore Command to use Unpivot
Colums QUESTION 7 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 - 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.
8
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.
Answer Exhibit
Explanation Table.AfterDelimeter("Changed
Type", Phone,Splitter.SplitTextByEachDelimiter("-
",QuoteStyle.Csv,false),"Area Code","Local
Phone") Phones.AfterDelimeter("Changed Type",
Phone,Splitter.SplitTextByEachDelimiter("-
",QuoteStyle.Csv,false),"Area Code","Local
Phone") QUESTION 8 You have the following
table.
9
You need to create a PivotTable as shown in the
following exhibit.

• What should you do?
• Add Product to the Rows area. Add LineTotal to
  the Values area twice.
• Add LineTotal to the Rows area. Add Product to
  the Values area twice.
• Add Product to the Rows area. Add LineTotal to
  the Columns area twice.
• Add LineTotal to the Rows area. Add Product to
  the Values area twice.
• Answer C

10

• QUESTION 9
• You have a table in 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 mode. The solution must ensure that you
  can create visualizations based on Price,
  Quantity, and Total.
• What should you do?
• Replace the Total column by using a measure.
• Replace the Total column by using a calculated
  column.
• From Query Editor, remove duplicate rows from the
  table.
• Move the Total column to a lookup table.
• Answer B
• QUESTION 10 DRAG DROP
• 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.
11
Answer Exhibit

• Explanation
• FORMAT(Date, "DDD") http//community.powerbi.com
  /t5/Desktop/Any-way-of- converting-numeric-
  month-amp-
• weeks-to-their/m-p/45128M17483
• QUESTION 11
• 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
  SalesResults.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?
• Run the Document Inspector.
• Save the workbook as an Excel Binary Workbook
  (.xlsb).
• From Query Editor, open the Data Source Settings
  and delete the credentials.
• Modify the source of the query.
• Answer C
• QUESTION 12
• 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?

12

• 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.
• Answer C
• QUESTION 13
• You open C\Data\Data,xlsx m Excel. 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?
• Decrypt the workbook.
• Disable iterative calculation for the workbook.
• Copy the file to a network share.
• Add a digital signature to the workbook.

13

• QUESTION 14
• 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?
• 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...
• Answer D
• QUESTION 15
• You have a workbook query that loads data from
  C\Data\Users.xlsx. You move Users.xlsx to a
  shared folder on the network. You need to ensure
  that you can refresh the data from Users.xlsx.
  What should you do?
• From the Linked Table tab in Power Pivot, modify
  the Update Mode.

14

• From PowerPivot Settings, modify the language
  options.
• From PowerPivot Settings modify the
  categorization options.
• From Workbook Connections, add a connection.
• Answer B
• QUESTION 17 DRAG DROP
• You have a table that contains the following data.

You need to create a PivotTable as shown in the
exhibit.
How should you configure the PivotTable? 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.
15
Answer Exhibit
Explanation Rows Product Values Quantity
Filters Country QUESTION 18 DRAG DROP You
have the following table named SalesOrderDetail
in a model.
You need to calculate the sum of SalesTotal for
all the rows that have a quantity greater than
1. 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.
16
NOTE Each correct selection is worth one point.
Answer Exhibit
Explanation CALCULATE(SUMX(SalesOrdersDetailS
alesTotal),SalesOrderDetailQuantity gt
1) QUESTION 19 DRAG DROP You have a data
model that contains a table named SalesOrders has
four columns named Orderld, SalesAmount,
OrderDate, and Territory. You plan to create a
PivotChart that will display the percentage of
SalesAmount for each Territory. You need to
create a measure to calculate the percentage of
sales of each territory. How should you complete
the DAX formula? To answer, drag the appropriate
value to the correct targets. Each value may be
used once, more than once, or not at all. You
need to drag the split bar between panes or
scroll to view content.
17
Answer Exhibit

• Explanation
• SUM(SalesAmount) / ALL(SalesOrders) /(FILTER ,
  ALL(SalesOrders))
• QUESTION 20
• You have an Excel workbook that contains a table
  named Sales. You add Sales to the Power Pivot
  model. You need to set a column named
  TransactionID as the row identifier for the
  Sales table. What should you do?
• From Power Pivot, modify the Table Behavior
  setting.
• From Query Editor, add an index column.
• From Query Editor, modify the Data Type.
• From Power Pivot, modify the Default Field Set.
• Answer A
• Explanation
• https//msdn.microsoft.com/en-us/library/hh560542(
  vsql.110).aspx
• ? In the Data View of your PowerPivot Window,
  click the PowerPivot Window Advanced Tab.

18
? In Reporting Properties, click Table
Behavior. ? Set the Row Identifier, and then
proceed to specify other properties in this
dialog. Opening the Table Behavior dialog box
https//ksdconsultancy.blog/2015/10/08/set-
table-behaviour-in-powerpivot/
19
Thank You
For Choosing our Quality Product 70-779 PDF Demo
For our 70-779 Exam Material as PDF and Simulated
Test Engine Please visit our Website http//www.
fravo.com/70-779-exams.html
Purchase This Exam on 15 discount Use our
Discount voucher "fravo15off" to get 15 discount
on this Product.
For more details and 24/7 help please visit our
Website http//www.fravo.com/