Microsoft Certified: Power BI Data Analyst AssociatePrepare the dataEasy
A data analyst is integrating customer data from a legacy system. The system exports data as a series of text files, each containing customer information where fields are separated by a pipe character ('|'). The customer address field, 'StreetAddress', sometimes contains commas within the address itself, which could be misinterpreted if not handled correctly. Which Power Query transformation should be used to correctly separate the fields into individual columns?
- ASplit Column by Delimiter
- BExtract Text Between Delimiters
- CParse JSON
- DSplit Column by Number of Characters
Show answer & explanationAnswer & explanation
Correct answer: A. Split Column by Delimiter
The 'Split Column by Delimiter' transformation is specifically designed for splitting a single text column into multiple columns based on a specified delimiter. Since the fields are separated by a pipe character, this is the most direct and appropriate method.
Why the other options are wrong
- B. This extracts a segment of text, not for splitting a whole column into multiple columns.
- C. This is for parsing JSON formatted data, which is not the case here.
- D. This splits a column based on a fixed length, not a delimiter.
Split Column by Delimiter
The 'Split Column by Delimiter' transformation in Power Query divides a single text column into multiple columns based on occurrences of a specified character or string.
- Splits one column into many.
- Requires a defined delimiter (e.g., comma, pipe, tab).
- Can handle multiple occurrences of the delimiter.
Memory trick: Delimiters are the scissors for your text lines.