Microsoft Certified: Power BI Data Analyst AssociatePrepare the dataHard
A data analyst is preparing a sales dataset in Power Query. The 'ProductID' column is intended to be a unique identifier, but profiling reveals leading and trailing spaces in some entries (e.g., ' P123 ' instead of 'P123'). Additionally, some entries are entirely whitespace or null. The analyst first needs to remove the leading/trailing spaces and then handle the problematic null/whitespace entries. What is the correct sequence of transformations?
- AClean, Trim, Fill Down.
- BTrim, Replace Values (blank/whitespace to null), Remove Empty.
- CReplace Errors, Trim, Filter Rows (remove nulls).
- DRemove Rows with Errors, Trim, Replace Values (nulls to empty string).
Show answer & explanationAnswer & explanation
Correct answer: B. Trim, Replace Values (blank/whitespace to null), Remove Empty.
The correct sequence is to first 'Trim' to remove leading/trailing spaces, then 'Replace Values' to convert any remaining blank/whitespace cells into actual nulls, and finally 'Remove Empty' to eliminate rows where 'ProductID' is now null or truly empty.
Why the other options are wrong
- A. 'Clean' removes non-printable characters, not spaces. 'Fill Down' is for missing values, not for cleaning unique identifiers.
- C. Replacing errors is for actual data type errors, not whitespace. 'Trim' is correct, but 'Filter Rows' for nulls might not catch blank strings if not converted to null first.
- D. Removing errors first might remove valid product IDs that just have spaces. 'Trim' should always come before handling nulls/blanks to correctly identify truly empty values.
Data Cleaning Sequence (Power Query)
The logical order of applying Power Query transformations to effectively clean and standardize data, often starting with formatting (trimming, casing) before addressing missing values, errors, or duplicates.
- Order matters for effective cleaning.
- General flow: Format -> Handle Missing/Errors -> Standardize -> Remove Duplicates.
- Trimming spaces should precede handling empty/null values.
Memory trick: First polish it, then check for holes, then throw out the trash.