NEW QUESTION 1
You have the PivotTable shown in the following exhibit.

You need to display only rows in the PivotTable in which the sum of LineTotal is greater than 100. What should you do?

• A. From Row Label, configure a Label filter.
• B. Add a slicer for LineTotal and select the values from the slicer.
• C. From Row Label, configure a Value Filter.
• D. Add LineTotal to the Filters area of PivotTable Field
• E. Configure the Filters value.

Answer: B

NEW QUESTION 2
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. an absolute value of 0
• B. the Total Sales Current Year measure
• C. an absolute value of 100
• D. the Total Sales Last Year measure

Answer: D

NEW QUESTION 3
From a workbook query, you import a table that has the following data.

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

What should you do?

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

Answer: A

NEW QUESTION 4
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?

• A. Change the Data Type of InvoiceDate and DateID to Text.
• B. Create a measure in Invoices that uses the Format DAX Function.
• C. Change the Data Type of DateID to Date.
• D. Create a calculated column in Invoices that uses the Format DAX function.

Answer: C

Explanation:
https://support.office.com/en-us/article/data-types-in-data-models-e2388f62-6122-4e2b-bcad-053e3da9ba90?ui=

NEW QUESTION 5
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.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Box 1: From the Extract menu, click Trim Box 2: From the Extract menu, click Clean

NEW QUESTION 6
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 2021
End of repeated scenario.
You need to add a custom column to the workbook query for Workshops that contains the email address of the workshop manager. The format of the email address is firstname.lastname@contoso.com.
How should you complete the query from Query Editor? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Text.Replace([Workshop Manager]," ",".")&Contoso.com

NEW QUESTION 7
You have an Excel workbook that displays two PivotCharts. One chart displays sales by month. The other 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 month PivotChart.
• B. Modify the Value Field Settings for the values of the sales by year PivotChart,
• C. Modify the Report Connections of the slicer.
• D. Remove all the fields from the Filters area of the sales by year PivotChart.

Answer: C

NEW QUESTION 8
You need to create a PivotChart as shown in the exhibit. (Click the Exhibit button.) Exhibit:

Which field should you use for each area? 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.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:

Legend: BrandName Axis: MonthName

NEW 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. 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 2021
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]”))

Answer: B

Explanation:
References:
https://support.office.com/en-us/article/cubevalue-function-8733da24-26d1-4e34-9b3a-84a8f00dcbe0 https://www.tutorialspoint.com/advanced_excel_functions/advanced_excel_cube_cubemember_function.htm

NEW QUESTION 10
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 2021
End of repeated scenario.
You create the column chart shown in the Pivot Chart exhibit. (Click the Exhibit button.)

You need to ensure that the month axis is displayed chronologically. What should you do?

• A. In the model, configure the Sort By Column setting for [Month] as [MonthID].
• B. In the model, configure the Sort By Column setting for (Month) as [Date].
• C. In PivotTable Fields, add [MonthID] to the Legend area.
• D. In PivotTable Fields, add [Date] to the Legend area.

Answer: B

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

Answer: A

Explanation:
Click the chart
On the Charts tab, under Change Chart Type, click Other, and then under Templates, click the chart template that you created.
https://stackoverflow.com/questions/17386777/how-to-apply-a-saved-chart-template-to-an-existing-chart

NEW QUESTION 12
You have a workbook query that loads the following table

You pivot the table on the Key column by using Value as the values column, and you receive the results shown in the following table.

You need to ensure that the data appears as shown in the following table.

What should you do?

• A. Change the aggregate value function of the pivot.
• B. Select the ID column, and then click Unpivot Columns
• C. Change the Data Type of the Value column.
• D. Delete the Picoted Column ste
• E. Select the Key column, and the click UnpivotColumns.

Answer: B

Explanation:
References:
https://support.office.com/en-us/article/unpivot-columns-power-query-0f7bad4b-9ea1-49c1-9d95-f588221c7098

NEW QUESTION 13
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

Answer: D

Explanation:
https://support.office.com/en-us/article/create-a-box-and-whisker-chart-62f4219f-db4b-4754-aca8-4743f6190f0

NEW QUESTION 14
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.
Why should you do?

• A. Select a cell in the worksheet and pressF5.
• B. From the data tab, click Refresh All.
• C. Close and open the workbook.
• D. From the Power Pivot tab, click Update All.

Answer: B

Explanation:
Refresh data from a Microsoft Query, the Data Connection Wizard, or web query
Click any cell in the range or table that contains the link to the external data.
On the Data tab, in the Connections group, click Refresh All.

To update only the selected data, click Refresh.
You can also right-click a cell in the range or table, and then click Refresh.
To update all the data connections in the workbook, click Refresh All.
Note: If you have more than one workbook open, you'll need to repeat the operation in each workbook. https://support.office.com/en-us/article/refresh-an-external-data-connection-in-excel-2021-for-windows-152417

NEW QUESTION 15
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?

• 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 Default Field Set for DimProduct and add ProductName to the Default Field.
• D. 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-columns-and-tables-in-power-pivot-ddf5b1f2-2ed2-4bdb-8f78-6f94

NEW QUESTION 16
You have a table that contains the following data.

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

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.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Box 1: Columns: Customers Box 2: Rows: Products
Box 3: Values: Quantity Box 4: Filters: Country

NEW QUESTION 17
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

Answer: C

NEW QUESTION 18
Note: This question is part of a series of questions that use the same scenario, For your convenience 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.

The data model is shown in the Data Model exhibit.

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 defined 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 Firstname Lastname, A value of 1 in the IsLatest column indicates that the workshop manager listed in the record is the current workshop manager.
Repair types has a list of all the repair types. Dates has a list of all the repair types.
Dates has a list of dates from 2015to 2021. 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 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.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
IF (ISBLANK([Total Hours]), BLANK(), CALCUATE [Total Hours], DATESADD (tblDates[Date]),
-1,MONTH)))

NEW QUESTION 19
You have the following table named SalesOrder Detail in a model.

You need to calculate the sum of SalesTotal for all the rows that have a quantity greater than 1.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
CALCULATE(SUMX(‘SalesOrdersDetail’[SalesTotal]),‘SalesOrderDetail’[Quantity] > 1)

NEW QUESTION 20
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 2021
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, 2021, the Value of Months Ago will be 0 for all the dates in July 2021, 1 for all the dates in June 2021, and 2 for all the dates in May 2021.
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.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Box 1: MONTH
Box 2: TODAY
References:
https://msdn.microsoft.com/en-us/library/ee634914.aspx https://msdn.microsoft.com/en-us/library/ee634567.aspx https://msdn.microsoft.com/en-us/library/ee634554.aspx

NEW QUESTION 21
......