Microsoft Certified: Power BI Data Analyst AssociatePrepare the dataMedium
A data analyst is importing data from a CSV file into Power BI. The CSV file contains a column named `EmployeeID` with values like 'EMP-001', 'EMP-002', 'EMP-003'. The analyst needs to ensure that only the numeric part of the ID (e.g., '001', '002', '003') is loaded into the data model and stored as an integer. Which Power Query transformation should be used to achieve this?
- AReplace Values
- BExtract Text Before Delimiter
- CExtract Text Between Delimiters
- DSplit Column by Delimiter
Show answer & explanationAnswer & explanation
Correct answer: D. Split Column by Delimiter
Splitting the column by delimiter '-' will create new columns. The second column will contain the numeric part ('001', '002'). This is a direct and efficient way to separate the components of the ID. Renaming the new column and changing its type to integer would be the subsequent steps.
Why the other options are wrong
- A. Replace Values could remove 'EMP-', but 'Split Column' is more idiomatic for structured IDs.
- B. Extract Text Before Delimiter would give 'EMP', which is not the desired numeric part.
- C. Extract Text Between Delimiters would require specifying the start and end delimiters, which is an alternative but 'Split Column' is often simpler for this pattern.
Split Column by Delimiter (Power Query)
A Power Query transformation that divides a single text column into multiple new columns based on a specified delimiter, allowing for easy parsing of structured strings.
- Creates new columns from one existing column
- Uses characters (e.g., comma, hyphen) as separation points
- Useful for parsing IDs, addresses, or concatenated values
Memory trick: Split Columns: Divide and Conquer the Data.