Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Excel 70-779 > examtopics > Flashcards

examtopics Flashcards

1
Q

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 an Excel workbook that contains a table named Table1. A sample of the data in Table1 is shown in the following table.

You need to create a PivotTable in PowerPivot as shown in the exhibit. (Click the Exhibit button.)

Solution: You create a measure named products the uses the DataTable DAX function. You add a PivotTable. You drag Products to the Rows field. You drag
Price to the Values field.
Does this meet the goal?

A) Yes
B) No

A

B) No

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

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 an Excel workbook that contains a table named Table1. A sample of the data in Table1 is shown in the following table.

You need to create a PivotTable in PowerPivot as shown in the exhibit. (Click the Exhibit button.)

Solution: You create a hierarchy named Products that contains ProductCategory, ProductSubCategory, and ProductName. You add a PivotTable. You drag
Products to the Rows field. You drag Price to the Values field.
Does this meet the goal?

A) Yes
B) No

A

B) No

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

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.

A

A) Yes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

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 Power Pivot model that contains the following tables.

There is a relationship between Products and ProductCategory.
You need to create a hierarchy in Products that contains ProductCategoryName and ProductName.
Solution: You create a measure that uses the USERELATIONSHIP DAX function.
Does this meet the goal?

A. Yes

B. No

A

B) No

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

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 Power Pivot model that contains the following tables.

There is a relationship between Products and ProductCategory.
You need to create a hierarchy in Products that contains ProductCategoryName and ProductName.
Solution: You create a calculated column that uses the RELATED DAX function
Does this meet the goal?

A. Yes

B. No

A

A) Yes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

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 Power Pivot model that contains the following tables.

There is a relationship between Products and ProductCategory.
You need to create a hierarchy in Products that contains ProductCategoryName and ProductName.
Solution: You create a measure that uses the ISCROSSFILTERED DAX function
Does this meet the goal?

A. Yes

B. No

A

B) No

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

HOTSPOT -
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 need to create a measure named [Sales Monthly RT] that calculates a running total of [Sales] for each date within a month as shown in the following exhibit.

How should you complete the DAX formula? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

A

CALCULATE([Sales],DATESMTD(DimDate[Datekey])

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

DRAG DROP -
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 need to create a chart as shown in the following exhibit.

A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

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 need to create a simplified view of the workbook for some users. The simplified view must only display data from FactSales, DimProduct, and DimDate.
What should you do in the data model?

A. Click Hide from Client Tools for all the tables except FactSales, DimProduct, and DimDate

B. Add the columns from FactSales, DimProduct, and DimDate to the Default Field Set

C. Create a new perspective

D. Modify the Table behavior settings for FactSales, DimProduct, and DimDate

A

C: Create a new perspective

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

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 have a PivotChart that uses Manufacturer as the axis and the sum of SalesAmount as the values.
You need to ensure that only the top 10 manufacturers appear in the chart.
What should you do?

A. Configure the Value Filters

B. Summarize the SaleAmount field by Max

C. Change the format of the SalesAmount field

D. Create a calculated column

A

A. Configure the Value Filters

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q
A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

You have a table that contains data relating to exam candidates and their associated exam 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. pie

C. box and whisker

D. histogram

A

C. box and whisker

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

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 Power Pivot, create a new calculated column, and then modify the KPI

C. From the properties of the KPI, modify the absolute value

D. From the properties of the KPI, modify the KPI base field

A

A. From Power Pivot, modify the measure

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

You have an Excel spreadsheet that contains a PivotChart.
You install Microsoft Power BI Publisher for Excel.
You need to add a tile for the PivotChart to a Power BI dashboard.
What should you do?

A. From powerbi.com, click Get apps

B. From powerbi.com, upload the Excel workbook

C. From the File menu in Excel, click Publish

D. From the Power BI tab in Excel, click Pin

A

D. From the Power BI tab in Excel, click Pin

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

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

A

C. From the Power Pivot model, edit the Synonyms

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

You open C:\Data\Data.xlsx in 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?

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

A

A. Decrypt the workbook

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q
A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q
A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

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 2018
End of repeated scenario.
When you attempt to create a relationship between DailyRepairs and Workshops, Power Pivot generates the following error message: “The relationship cannot be created because each column contains duplicate values. Select at least one column that contains only unique values”.
You need to ensure that you can create a valid relationship between the tables.
What should you do?

A. In the Power Pivot model, change the data type for Workshop[ID] to General

B. In the workbook query for Workshops, add an index column

C. In the Power Pivot model, change the Table Behavior setting for Workshops

D. In the workbook query for Workshops, filter [IsLatest] to equal 1

A

D. In the workbook query for Workshops, filter [IsLatest] to equal 1

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

DRAG DROP -
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 2018
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.
Select and Place:

VALUES: BLANK, CALCULATE, DATEADD, DATESBETWEEN, IF, NULL, -1, 1

VALUE(ISBLANK([Total Hours]), VALUE(), CALCULATE([Total Hours], VALUE(tblDates[Date], VALUE, MONTH)))

A

IF(ISBLANK([Total Hours]), BLANK(), CALCULATE([Total Hours], DATEADD(tblDates[Date], -1, MONTH)))

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

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 2018
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?

A. =CUBEMEMBER(“ThisWorkbookDataModel”, “[DailyRepairs]. [Avg Revenue Per Hour]”, CUBEMEMBER (“ThisWorkbookDataModel”, “[Dimensions]. [Repair Type]. [Engine]”))

B. =CUBEVALUE(“ThisWorkbookDataModel”, “[Measures]. [Avg Revenue Per Hour]”, CUBEMEMBER (“ThisWorkbookDataModel”, “[Dimensions]. [Repair Type]. [Engine]”))

C. =CUBEMEMBER(“ThisWorkbookDataModel”, “[DailyRepairs]. [Avg Revenue Per Hour]”, CUBEMEMBER (“ThisWorkbookDataModel”, “[RepairTypes]. [Repair Type]. [Engine]”))

D. =CUBEVALUE(“ThisWorkbookDataModel”, “[Measures]. [Avg Revenue Per Hour]”, CUBEMEMBER (“ThisWorkbookDataModel”, “[RepairTypes]. [Repair Type]. [Engine]”))

A

D. =CUBEVALUE(“ThisWorkbookDataModel”, “[Measures]. [Avg Revenue Per Hour]”, CUBEMEMBER (“ThisWorkbookDataModel”, “[RepairTypes]. [Repair Type]. [Engine]”))

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

HOTSPOT -
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 2018
End of repeated scenario.
To the Dates table, you need to add a calculated column named Months Ago. Months Ago must display the number of calendar months before the current month.
For example, if the current date is July 10, 2017, the Value of Months Ago will be 0 for all the dates in July 2017, 1 for all the dates in June 2017, and 2 for all the dates in May 2017.
How should you complete the DAX formula? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

  • CALCULATE* (TODAY())-MONTH([Date])+((YEAR(DATE ())-[Year])*12)
  • DATEDIFF* DATESYTD
  • MONTH* DATEVALUE
  • YEAR* TODAY
A

MONTH (TODAY())-MONTH([Date])+((YEAR(TODAY())-[Year])*12)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

DRAG DROP -
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.
Select and Place:

A
  1. From Data Tab, create a new qury
  2. Click from folder, then add the folder path.
  3. Edit the query, and then click combine binaries.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q
A

C. From the Data tab, click From Table/Range

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

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. Append Queries

B. Combine Binaries

C. Transpose

D. Merge Queries

A

A. Append Queries

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

You have 20 workbook queries that load 20 CSV files to a local computer.
You plan to send the workbook and the 20 CSV files to several users. The users will store the files in various locations.
You need to ensure that the users can change the path to the CSV files in the queries as quickly as possible.
What should you do from Query Editor?

A. Append all the queries. Edit the source of the first query

B. Merge all the queries. Edit the source of the first query

C. For each query, create a new query that uses a reference. Modify the source of each new query

D. Create a parameter. Modify the source of each query to use the parameter

A

D. Create a parameter. Modify the source of each query to use the parameter

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

You have multiple workbook queries that load data from tables in Microsoft Azure SQL Database to a Power Pivot data model.
You discover that new rows were added to the tables in Azure SQL Database.
You need to ensure that the workbook has the new data.
What should you do?

A. From the Data tab, click Refresh All

B. From the Power Pivot tab, click Update All

C. Close and open the workbook

D. Select a cell in the worksheet and press F5

A

A. From the Data tab, click Refresh All

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

DRAG DROP -
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.
Select and Place:

A

Clean and Trim

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q
A

TestAScore, TestBScore, TestCScore

Unpivot Columns

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

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. Modify the source of the query

B. Save the workbook as an Excel Binary Workbook (.xlsb)

C. From Query Editor, open the Data Source and delete the credentials

D. Run the Document Inspector

A

D. Run the Document Inspector

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

You have an Excel workbook query that loads data to a worksheet and the data model.
You need to ensure that the data is refreshed whenever you open the workbook.
What should you do?

A. From the File tab, click Options, and then modify the General options

B. From the Power Pivot model, modify the Table Behavior setting

C. From the File tab, click Options, and then modify the Data options

D. Run the Data tab, click Queries & Connections, and then edit the properties of the query

A

D. Run the Data tab, click Queries & Connections, and then edit the properties of the query

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q
A

1- “Split Column by Delimter”
2- “Merge Columns”

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q
A

A. Left Outer

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q
A

A. Change the Aggregate Value Function of the pivot

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q
A

C. From the model, create a hierarchy

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q
A
  1. Create a Pivot Table
  2. Create a Measure
  3. Create a KPI
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q
A

D. Sort the [Month Name] column by [Month Number]

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
38
Q
A

D. Click the - button

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
39
Q
A

Divide([TotalSales], CALCULATE( [TotalSales], ALL(‘Sales’)))

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
40
Q

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 measure

B. Replace the Total column by using a calculated column

C. From Query Editor, remove duplicate rows from the table

D. Move the Total column to a lookup table

A

A. Replace the Total column by using a measure

41
Q

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 Whole Number. DateID is in the format of YYYYMMDD.
You need to create a relationship between Date and Invoices.
What should you do first?

A. Change the Data Type of InvoiceDate and DateID to Text

B. Create a calculated column in Invoices that uses the FORMAT DAX function

C. Change the Data Type of DateID to Date

D. Create a measure in Invoices that uses the FORMAT DAX function

A

C. Change the Data Type of DateID to Date

42
Q
A
  1. FullAlternateDateKey
  2. OrderDate
43
Q

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: From Query Editor, you configure the Data source settings.
Does this meet the goal?

A. Yes

B. No

A

A. Yes

44
Q

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: From Query Editor, you edit the source of each table query.
Does this meet the goal?

A. Yes

B. No

A

A. Yes

45
Q

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 create a new connection to Production1, and then you import the tables.
Does this meet the goal?

A. Yes

B. No

A

B. No

46
Q
A

B. No

47
Q
A

B. No

48
Q
A

A. Yes

49
Q
A

A. Create a PivotTable. Add UnitPrice to the Rows area and add SalesAmount to the Values area. Right-click a cell value for UnitPrice and modify the Group settings.

50
Q
A

C. Select a wedge of the pie chart, and then drag the wedge

51
Q
A

D. On the Design tab, click Report Layout, and then click Show in Tabular Form

52
Q

You import the data from two next files into a PowerPivot model to create two tables named Customers and Invoices. Each table contains a column named
CustomerID.
When you attempt to create a relationship between the Customers table and the Invoices table by using the CustomerID column from each table, the relationship cannot be created due to duplicate CustomerID values.
You need to ensure that you can create the relationship.
What should you do?

A. Add an index column to the Customers query

B. Add an index column to the Invoices query

C. Group the Customers query by CustomerID

D. Sort the Invoices query by CustomerID, and then add a Fill Down step

A

C. Group the Customers query by CustomerID

53
Q
A

B. Group by Vendor_ID and add a SUM aggregation

54
Q
A

C. Modify the relationships

55
Q

Your network contains a folder that has data files in 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 Group By command on the Home 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 Count Values command on the Transform tab.

D. Create a query that uses a folder source, and then use the Count Values command on the Transform tab.

A

B. Create a query that uses a folder source, and then use the Group By command on the Home tab.

56
Q
A
  1. a linearforecast trendline
  2. the standard deviation
57
Q
A

C. Data Table

58
Q
A
  1. COUNTX
  2. FILTER
59
Q
A

Sales By Territoy = SUM(SalesAmount) / CALCULATE(SUM(SalesAmount), ALL(SalesOrders))

60
Q
A

D. Right-click a bar in the PivotChart and click Collapse Entire Field

61
Q
A

A. Yes

62
Q
A

B. No

63
Q
A

A. Yes

64
Q
A
  1. % of Parent Total
  2. % of Column Total
65
Q

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?

A. Split the column by delimiter

B. Split the column by characters

C. Configure the Data Type

D. Round the column

A

C. Configure the Data Type

66
Q
A

Text.Replace(Text.End ([Phone], 12 ), “ “, “-“)

67
Q

You have a workbook query that loads data from a table named Products.
Products contains a column named InternalPrice that has a Data Type of Decimal.
From Query Editor, you create a custom column named ResellerPrice that uses a formula to multiply InternalPrice by 1.2, and then you remove the InternalPrice column.
What will occur when you load the data to a worksheet?

A. An error message will appear and all the columns except InternalPrice and ResellerPrice will load to the worksheet.

B. All the columns except InternalPrice will load to the worksheet. The values in ResellerPrice will be correct.

C. All the columns except InternalPrice will load to the worksheet. The values in ResellerPrice will be null.

D. An error message will appear and all the data will fail to load.

A

B. All the columns except InternalPrice will load to the worksheet. The values in ResellerPrice will be correct.

68
Q
A

Table.SplitColumn(#’Changed Type”, “Phone”, Splitter.SplitTextByEachDelimiter({“-“}, QouteStyle.Csv, false), {“Arena Code”, “Phone Number”})

69
Q
A

D. From the Split Column menu, click By Delimiter

70
Q

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?

A. From Query Editor, modify the Source step

B. From the Insert tab in Excel, click My Add-ins, and then manage the add-ins

C. From the Linked Table tab in Power Pivot, modify the Update Mode

D. From the Data tab in Excel, click Connections, and then modify the properties of the connection

A

A. From Query Editor, modify the Source step

71
Q

You have a workbook query that gets a table from an Excel workbook. The table contains a column named 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 to 1.
What should you do from Query Editor?

A. Select the column and click Replace Errors”¦

B. Select the table and click Keep Errors.

C. Select the column and Click Replace Values”¦

D. Select the column and click Remove Errors.

A

A. Select the column and click Replace Errors”¦

72
Q

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?

A. From Query Editor, modify the Data Type

B. From Power Pivot, modify the Table Behavior settings

C. From Query Editor, add an index column

D. From Power Pivot, modify the Default Field Set

A

B. From Power Pivot, modify the Table Behavior settings

73
Q

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 Product Name.
What should you do?

A. Create a perspective that has only the ProductName field from DimProduct selected.

B. Select all the columns in DimProduct except ProductName, right-click the columns, and then click Hide from Client Tools.

C. Edit the Table behavior settings for DimProduct and add ProductName to the Default Label.

D. Edit the Default Field Set for DimProduct and add ProductName to the Default Field.

A

A. Create a perspective that has only the ProductName field from DimProduct selected.

74
Q
A

CALCULATE(SUM(‘SalesOrderDetai’[SalesTotal])’SalesOrderDetail’[Quantity]>1)

75
Q
A

FORMAT([Date], “DDD”)

76
Q

You are building a KPI.
You need to configure the KPI to display a red icon when the sales from a month is less than nine percent of the sales from the last 12 months.
What should you use to define the target value?

A. a measure

B. a calculated column

C. a calculated field

D. an absolute value

A

A. a measure

77
Q
A

A. 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.

78
Q

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. LOOKUP

C. VLOOKUP

D. CUBESET

A

A. CUBEVALUE

79
Q
A

B. To DimProduct, add a calculated column named ProductSubcategoryName that uses the LOOKUPVALUE(DimProductSubcategory [ProductSubcategoryName],DimProductSubcategory[ProductCategoryKey],[ProductSubcategoryKey]) DAX formula.

80
Q

You have a measure named SalesGrowth that calculates the percent of sales growth. The measure uses the following formula.
([Total Sales Current Year] “” [Total Sales Last year]) / [Total Sales Last Year]
Total Sales Current Year is a measure that calculates the sales from the current calendar year. Total Sales Last Year is a measure that calculates the sales from the previous calendar year.
You need to create a KPI that displays a red icon when the sales growth is less than last year.
What should you use to define the target value?

A. the Total Sales Current Year measure

B. an absolute value of 0

C. the Total Sales Last Year measure

D. an absolute value of 100

A

B. an absolute value of 0

81
Q
A

“Qtr “& ROUNDUP(MONTH([Date])/3, 0)

82
Q

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:

A

C. Mark DimDate as the date table

83
Q

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 exhibit. (Click the Exhibit button.)
Exhibit:

A

C. combo

84
Q

Your company has a data analyst who uses Microsoft Power BI Desktop to create a data model and several reports.
The data analyst publishes the reports to the Power BI service.
You need to create a PivotTable in Excel that uses the data model created by the data analyst. The solution must prevent the data from being imported into Excel.
What should you do first?

A. From Excel, create a new query that uses the Data Catalog

B. From powerbi.com, select the report. From the File menu, click Download report

C. From powerbi.com, select the report and click Analyze in Excel

D. From powerbi.com, select the report. From the File menu, click Save us

A

C. From powerbi.com, select the report and click Analyze in Excel

85
Q
A

Column: Year
Rows: Channel Name and Promotion Type
Values : Total sales

86
Q
A

D. From Row Label, configure a Value Filter

87
Q
A

Stacked (clustered) column and no
ine and yes

88
Q
A

C. Add Product to the Rows area. Add LineTotal to the Values area twice.

89
Q

You install Microsoft Power BI Publisher for Excel.
You need to use Excel to connect and analyze Power BI data.
To which two types of Power BI data can you connect? Each correct answer presents a complete solution.
NOTE: Each correct selection is worth one point.

A. datasets

B. apps

C. reports

D. dashboard

A

A. datasets
C. reports

90
Q

You have an Excel workbook that displays two PivotCharts. One chart displays sales by month. The order chart displays sales by year.
You add a slicer for month.
You discover that when you select a month in the slicer, the data in the sales by year PivotChart changes.
You need to prevent the slicer from affecting the sales by year PivotChart.
What should you do?

A. Remove all the fields from the Filters area of the sales by year PivotChart

B. Modify the Report Connections of the slicer

C. Modify the Value Field Settings for the values of the sales by year PivotChart

D. Remove all the fields from the Filters area of the sales by month PivotChart

A

B. Modify the Report Connections of the slicer

91
Q
A

Text.Replace([Workshop Manager], “ “, “.”)&”@Contoso.com”

92
Q
A

B. In the model, configure the Sort By Column setting for [Month] as [MonthID]

93
Q
A

C. conditional formatting

94
Q

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. On the Format tab, click Format Selection

B. Right-click the chart, and then click Format Chart Area

C. On the Design tab, click Change Chart Type

D. Right-click the chart, and then click PivotChart Options

A

C. On the Design tab, click Change Chart Type

95
Q
A

C. In the data model, modify the Sort by Column setting for Sales[Date] (unsure)

96
Q
A

Remove blanks, change data type and remove errors

97
Q

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. Append Queries

B. Combine Binaries

C. Transpose

D. Merge Queries

A

A. Append Queries

98
Q

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. the Microsoft Power BI service only

C. Excel and the Microsoft Power BI service

D. Excel only

A

A. Microsoft Power BI Desktop

Excel 70-779 (1 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions