You use Power Query Editor to preview a query that contains sales order data in the following columns:
• Tax Amount
• Sales Order ID
• Freight Amount
• Subtotal Amount
• Total Item Quantity
The Sales Order ID column uniquely identifies each sales order. The Subtotal Amount and Total Item Quantity columns are always populated, but the Tax Amount and Freight Amount columns are sometimes null when an order has no associated amount.
You need to query the data to identify the following metrics by month:
• The average item quantity per order
• The average freight amount per order
• The maximum item quantity per order
How should you modify the query?
A. In the Total Item Quantity column, replace the null values with 0.
B. In the Tax Amount column, remove rows that contain null values.
C. In the Freight Amount column, remove rows that contain null values.
D. In the Freight Amount column, replace the null values with 0.
Show Answer
Correct Answer: D
Explanation: The metrics are calculated per order, and orders without freight should still be included. Because a null Freight Amount indicates no associated freight charge, it should be treated as 0 so that averages and maximums are calculated across all orders correctly. Removing rows would exclude valid orders and distort results, and other columns are either always populated or not part of the required metrics.
Question 120
You have a PBIX file that imports data from a Microsoft Excel data source stored in a file share on a local network.
You are notified that the Excel data source was moved to a new location.
You need to update the PBIX file to use the new location.
What are three ways to achieve the goal? Each correct answer presents a complete solution.
NOTE: Each correct selection is worth one point.
A. From the Datasets settings of the Power BI service, configure the data source credentials.
B. From the Data source settings in Power BI Desktop, configure the file path.
C. From Current File in Power BI Desktop, configure the Data Load settings.
D. From Power Query Editor, use the formula bar to configure the file path for the applied step.
E. From Advanced Editor in Power Query Editor, configure the file path in the M code.
Show Answer
Correct Answer: B, D, E
Explanation: The Excel file location can be updated directly in Power BI Desktop. You can change the source path via Data source settings, or edit the Source step in Power Query either through the formula bar or by modifying the M code in the Advanced Editor. Configuring credentials in the service or Data Load settings does not change the file path.
Question 121
HOTSPOT
-
You have a folder that contains 50 JSON files.
You need to use Power BI Desktop to make the metadata of the files available as a single dataset. The solution must NOT store the data of the JSON files.
Which type of data source should you use, and which transformation should you perform? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Show Answer
Correct Answer: Folder
Delete the Content column
Explanation: Using the Folder data source lists all files with their metadata. Removing the Content (binary) column ensures no JSON file data is stored, leaving only file-level metadata as the dataset.
Question 122
You have the CSV file shown in the following table.
You use Power Query Editor to preview the data in the file.
You need to transform the data to meet the following requirements:
• The first column must contain the month.
• The second column must contain the year.
• The third column must contain the order amount for the month and year.
Which transformation should you use first?
A. remove
B. unpivot
C. transpose
D. pivot
Show Answer
Correct Answer: B
Explanation: The source CSV is a cross-tab layout with months as column headers and values spread across multiple columns. To end up with a normalized structure where each row represents a Month–Year combination with a single order amount, the first required step is to convert those month columns into rows. The Unpivot transformation does exactly this by turning column headers (months) into values in a single column while keeping the year and amount aligned. Other options either remove data, change orientation incorrectly, or are applied later in the process.
Question 123
You have a Power BI report. The report contains a line chart that displays sales data for several regions.
You need to add an element to the report that will enable users to filter the sales data to include only a selected region.
Which two elements achieve the goal? Each correct answer presents a complete solution.
NOTE: Each correct selection is worth one point.
A. a slicer visual
B. a drillthrough filter
C. a table visual
D. a card visual
E. a Key Performance Indicator (KPI) visual
Show Answer
Correct Answer: A, C
Explanation: A slicer visual is designed specifically to let users filter report visuals by selecting values such as a region, directly affecting the line chart. A table visual can also act as a filter through visual interactions: selecting a region row in the table filters the line chart to that region. Drillthrough requires navigating to another page and does not filter the existing chart directly, while card and KPI visuals do not provide filtering capability.
Question 124
You have a Power BI dataset that contains quarterly sales performance data.
You need to enable managers to review the data in a format that meets the following requirements:
• Is optimized for printing.
• Renders data in Microsoft Excel, Word, PowerPoint, and PDF formats.
What should you create?
A. a template app
B. a dashboard
C. a paginated report
D. an interactive report
Show Answer
Correct Answer: C
Explanation: Paginated reports are designed for pixel-perfect layouts optimized for printing and formal distribution. They can render consistently to Excel, Word, PowerPoint, and PDF formats, which directly meets the stated requirements. Dashboards and interactive reports are optimized for on-screen interaction, not printing.
Question 126
HOTSPOT -
You have Power BI report that contains the fields shown in the following exhibit.
Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic.
NOTE: Each correct selection is worth one point.
Show Answer
Correct Answer: two explicit measures
Summarization setting
Explanation: The visual uses two implicit measures (Sum of Sales Amount and Sum of Product Cost), so two explicit measures are required to replace them. Default aggregation behavior for a field across additional visuals is controlled by the field’s Summarization setting.
Question 127
You have a Power BI data model that contains two tables named Sales and Date. The Sales table contains three measures named Order Quantity, Product Cost, and Sales Amount.
You need to create the visual shown in the following exhibit.
In which section of the Fields well should you place the measures?
A. Columns
B. Rows
C. Values
D. Drill through
Show Answer
Correct Answer: C
Explanation: In Power BI, measures can only be placed in the Values well of a visual. To create the shown matrix with multiple measures (Order Quantity, Product Cost, Sales Amount) listed, you add all measures to Values and, if needed, enable "Switch values to rows" in the matrix formatting. Rows and Columns are for categorical fields, and Drill through is not used for displaying measures.
Question 128
You are reviewing a Power BI data model.
You have a calculated table that has the following definition.
ProductList = INTERSECT ( ProductsGroupA, ProductsGroupB )
You need to identify the results of the DAX expression.
Which rows will be returned in ProductList?
A. all the rows in ProductsGroupB that have a matching row in ProductsGroupA
B. all the rows in both tables
C. all the rows in ProductsGroupA that have a matching row in ProductsGroupB
D. all the rows in ProductsGroupA that have no matching row in ProductsGroupB.
Show Answer
Correct Answer: C
Explanation: INTERSECT(table_expression1, table_expression2) returns only the rows from the first table that also exist in the second table. It is not commutative, and duplicates from the first table are preserved if they also appear in the second. Therefore, ProductList contains all rows in ProductsGroupA that have a matching row in ProductsGroupB.
Question 129
You have a Power BI report that contains the visuals shown in the following table.
You need to modify the location of each visual.
What should you modify for each visual?
A. the layer order
B. the padding
C. the position
D. the tab order
Show Answer
Correct Answer: C
Explanation: To change where a visual appears on a Power BI report page, you must modify its Position property (X and Y coordinates). Layer order controls stacking, padding controls internal spacing, and tab order controls navigation—not physical location.
$19
Get all 356 questions with detailed answers and explanations
Instant download HTML + PDF delivered the moment payment clears.
Secure Stripe checkout we never see or store your card details.
7-day refund if files are defective see our refund policy.
