Microsoft Certified: Power BI Data Analyst AssociatePrepare the dataEasy
A data analyst is importing a dataset into Power BI from a CSV file. The file contains a column named 'Product_ID' which is intended to be a unique identifier, but upon initial inspection in Power Query Editor, it appears to contain leading and trailing spaces in some entries. Which Power Query transformation should be applied FIRST to ensure data consistency for this column?
- AReplace Values
- BTrim
- CChange Data Type to Text
- DRemove Duplicates
Show answer & explanationAnswer & explanation
Correct answer: B. Trim
The presence of leading and trailing spaces can cause issues with data uniqueness and joins. Trimming the column removes these extraneous spaces, making the data consistent before further transformations like removing duplicates or changing data types.
Why the other options are wrong
- A. Replacing values is used for specific character or string substitutions, not for general removal of leading/trailing spaces.
- C. Changing the data type before trimming would retain the spaces, potentially causing issues with subsequent operations.
- D. Removing duplicates should be done after trimming to ensure that 'Product_ID' values that are only different by spaces are considered the same.
Trim Transformation
The Trim transformation in Power Query removes leading and trailing whitespace characters from text values in a column.
- Removes spaces from the beginning and end of text.
- Essential for data cleaning and consistency.
- Applied to text columns.
Memory trick: Clean before you link, so every piece fits.