PL-300 · Question #382
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…
The correct answer is D. In the Freight Amount column, replace the null values with 0. When calculating averages for columns that can contain null values, replacing nulls with zero ensures that orders with no value for that metric are correctly included in the average calculation.
Question
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?
Options
- AIn the Total Item Quantity column, replace the null values with 0.
- BIn the Tax Amount column, remove rows that contain null values.
- CIn the Freight Amount column, remove rows that contain null values.
- DIn the Freight Amount column, replace the null values with 0.
How the community answered
(32 responses)- A9% (3)
- B3% (1)
- C3% (1)
- D84% (27)
Why each option
When calculating averages for columns that can contain null values, replacing nulls with zero ensures that orders with no value for that metric are correctly included in the average calculation.
Replacing null values in 'Total Item Quantity' with 0 is unnecessary because the question states this column is 'always populated', implying no nulls exist.
Removing rows with null values in 'Tax Amount' would exclude entire sales orders from the analysis, potentially skewing other metrics like 'average item quantity per order' if those orders contained valid item quantities.
Removing rows with null values in 'Freight Amount' would exclude all orders that had no freight, leading to an average that only reflects orders with positive freight amounts, thus inaccurately representing the average freight across all orders.
To accurately calculate the average freight amount per order, orders with no freight (represented by null values) must be included as having a freight amount of 0; replacing nulls with 0 ensures these orders contribute correctly to the average instead of being excluded or causing errors.
Concept tested: Power Query data cleansing for aggregation
Source: https://learn.microsoft.com/en-us/power-query/replacevalues
Community Discussion
No community discussion yet for this question.