Page: 1
/ 7
Total 101 questions
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 the following data.
You need to retrieve a list of the unique ProductName entries.
Solution: Create a PivotTable that uses the ProductName field in the Rows area.
Does this meet the goal?
- A. Yes
- B. No
Answer : B
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 the following data.
You need to retrieve a list of the unique ProductName entries.
Solution: Select the ProductName column, and then click Group on the Data tab.
Does this meet the goal?
- A. Yes
- B. No
Answer : B
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 the following data.
You need to retrieve a list of the unique ProductName entries.
Solution: Open the Advanced Filter dialog box, select Filter the list, in-place, and then select Unique records only.
Does this meet the goal?
- A. Yes
- B. No
Answer : A
HOTSPOT -
You have a model that contains data relating to corporate profits. The model contains a measure named Profit.
You need to create a PivotTable to display the Profit measure in three different formats by using the Show Value As feature. The PivotTable must produce the results shown in the following table.
How should you configure the Show Value As feature for % Profit of Annual Total and % profit of Grand Total? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
Answer :
Reference:
https://www.contextures.com/xlPivot10.html
https://support.office.com/en-us/article/calculate-a-percentage-for-subtotals-in-a-pivottable-6aa66bce-11c5-47f7-b6b6-0959b0b13a72
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
Answer : C
Reference:
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/currency-data-type https://support.office.com/en-us/article/format-numbers-as-currency-0a03bb38-1a07-458d-9e30-2b54366bc7a4
DRAG DROP -
You have a workbook query that retrieves data from a table named Users. Users contains a column named PhoneNumber. The following is a sample of the data in PhoneNumber.
514 555 0160
1 (11) 500 555-0122
128 555-0148
819 555-0186
1-996-555-0192
+1 138-555-0156
556-555-0192
You need to create a custom column that contains the data in PhoneNumber in the format of 999-999-9999. The following is a sample of the desired data.
514-555-0160
500-555-0122
128-555-0148
819-555-0186
996-555-0192
138-555-0156
556-555-0192
How should you complete the Query Editor 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.
Select and Place:
Answer :
Reference:
https://docs.microsoft.com/en-us/powerquery-m/text-replacerange https://docs.microsoft.com/en-us/powerquery-m/text-end
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.
Answer : C
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 of 999-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.
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.
Select and Place:
Answer :
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 Extract menu, click Last Characters
- D. From the Split Column menu, click By Delimiter
Answer : A
Reference:
https://www.pcworld.com/article/3163966/excel-tutorial-how-to-import-and-parse-complicated-data.html
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
Answer : D
Reference:
https://support.office.com/en-gb/article/connection-properties-9d3599a9-e9b3-461d-99b2-c5505ddae6e0
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.
Answer : A
Reference:
https://www.dutchdatadude.com/power-bi-pro-tip-dealing-with-errors-when-reading-excel-files/
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
Answer : B
Reference:
https://support.office.com/en-us/article/set-table-behavior-properties-for-power-view-reports-c0e8c95e-5bb0-4bd8-a86c-6013301700ca
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.
Answer : B
Reference:
https://support.office.com/en-us/article/hide-columns-and-tables-in-power-pivot-ddf5b1f2-2ed2-4bdb-8f78-6f94503ca87a
DRAG DROP -
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.
Select and Place:
Answer :
Reference:
https://exceleratorbi.com.au/use-sum-vs-sumx/
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.
Select and Place:
Answer :
Reference:
https://docs.microsoft.com/en-us/dax/custom-date-and-time-formats-for-the-format-function
30 days access 