Microsoft Certified Power Bi Data Analyst Associate · Free Practice Question Medium
Question 6
You are preparing the sales dataset for analysis in Power Query. You observed that there are errors in the dataset, as shown in the image:
You need to ensure the Discount Band column is complete and usable in the final report. How should you handle the null values in the Discount Band column?
-
A
Change the data type of the column to "Whole Number" to remove nulls.
-
B
Filter out rows with null values.
-
C
Leave the null values as is because Power BI will handle them automatically.
-
D
Replace null values with "Unknown" using the "Replace Values" option.
Reveal correct answer
Correct answer: D
Explanation
Requirements Breakdown:
Complete column: All rows in Discount Band should have meaningful values, avoiding gaps that could confuse analysis.
Usable in report: The data should support visuals and calculations without errors or misinterpretation caused by nulls.
Context: Nulls indicate missing discount categories, and the solution should preserve all rows unless explicitly stated otherwise.
Solution Steps:
The best approach is to replace null values with a placeholder like "Unknown" using Power Query’s "Replace Values" feature:In Power Query Editor, select the Discount Band column.
Right-click the column header, choose "Replace Values," or go to "Home" > "Replace Values."
In the dialog, set "Value to Find" as null (leave blank or use the null keyword, depending on the UI), and "Replace With" as "Unknown."
Apply the transformation and load the data.
The Microsoft documentation on replacing values in Power Query states, "Replace Values can substitute nulls with a specified value, ensuring data consistency without removing rows."
Why This Works:
Preserves Data: Replacing nulls with "Unknown" keeps all rows intact, avoiding data loss, which is critical for a sales dataset where every record (e.g., sale) may matter.
Usability: "Unknown" is a clear category that can be used in visuals (e.g., pie charts) or filters, preventing null-related errors in Power BI. Unlike nulls, it’s explicitly handled in DAX or visuals.
Completeness: The column becomes fully populated, meeting the requirement for a complete and usable state.
Why Not Other Options?:
Filter out rows with null values: This removes rows with nulls in Discount Band, potentially discarding valuable sales data (e.g., sales amounts or dates) just because the discount category is missing. Without a directive to exclude these rows, this risks incomplete analysis.
Change data type to Whole Number: Discount Band sounds like a categorical field (e.g., text labels), not numeric. Changing it to "Whole Number" would error out on text values or nulls, not resolve them, per the data type documentation.
Leave null values as is: While Power BI can display nulls (e.g., as blanks in visuals), they can complicate analysis (e.g., missing categories in slicers) or DAX calculations, reducing usability.
A.
Change the data type to "Whole Number": This is incorrect. The column represents categories, not numbers, so changing the data type is inappropriate.
B.
Filter out rows with null values: This is incorrect. Filtering rows would result in data loss, which may not be acceptable for analysis.
C.
Leave null values as is: This is incorrect. Null values may cause issues in visualizations and calculations.
D.
Replace null values with "Unknown": This is correct. Replacing null values with a meaningful placeholder like "Unknown" ensures data completeness without losing information.
Discussion
Think the marked answer is wrong, or have a better explanation? Share it below — comments appear after review.
