Microsoft Certified: Power BI Data Analyst AssociatePrepare the dataMedium
You are working with a dataset in Power Query where a column named 'Product_Code' contains values like 'P-12345', 'A-67890', 'B-11223'. You need to extract only the numeric part (e.g., '12345', '67890', '11223') from this column. The prefix (e.g., 'P-', 'A-', 'B-') can vary in length but always ends with a hyphen. Which Power Query transformation step is the most appropriate to achieve this?
- ASplit Column by Delimiter, using '-' as the delimiter and choosing 'Right-most delimiter'.
- BSplit Column by Delimiter, using '-' as the delimiter and choosing 'Left-most delimiter'.
- CExtract > Text After Delimiter, using '-' as the delimiter.
- DExtract > Last Characters, specifying a fixed number of characters.
Show answer & explanationAnswer & explanation
Correct answer: C. Extract > Text After Delimiter, using '-' as the delimiter.
The 'Extract > Text After Delimiter' transformation is ideal here because it directly returns the portion of the text after the specified delimiter ('-'). This handles varying prefix lengths as long as the delimiter is consistent.
Why the other options are wrong
- A. Splitting by 'Right-most delimiter' would be appropriate if the prefix was always the same and the hyphen could appear multiple times, but 'Extract > Text After Delimiter' is more direct for this scenario.
- B. Splitting by 'Left-most delimiter' would give two columns: the prefix and the rest, requiring an additional step to remove the first column.
- D. The numeric part varies in length, so a fixed number of characters would not work correctly for all values.
Extract Text After Delimiter
A Power Query text transformation that returns the portion of a text string that appears after a specified delimiter.
- Useful when you need the suffix of a string.
- Handles varying prefix lengths automatically.
- The delimiter itself is not included in the result.
Memory trick: Need what's AFTER? Use 'After Delimiter' to capture.