Microsoft Certified: Fabric Analytics Engineer AssociatePrepare and transform data (20-25%)Easy
A data engineer is working on a Dataflow Gen2 to ingest product data from a legacy CSV file that uses a semicolon (`;`) as a delimiter instead of a comma. The file also contains a header row. Which option in the Power Query Editor's Text/CSV connector configuration should the engineer adjust to correctly parse this file?
- AModify the 'Delimiter' option to 'Semicolon'.
- BAdjust the 'Quote character' setting.
- CChange the 'Origin' to 'Unicode (UTF-8)'.
- DSet 'Data Type Detection' to 'Do not detect data types'.
Show answer & explanationAnswer & explanation
Correct answer: A. Modify the 'Delimiter' option to 'Semicolon'.
The 'Delimiter' option in the Power Query Editor's Text/CSV connector configuration is specifically used to specify the character that separates columns in the text file. For a CSV file using a semicolon, changing this setting to 'Semicolon' will ensure the data is parsed into columns correctly.
Why the other options are wrong
- B. The 'Quote character' handles how fields containing delimiters are enclosed (e.g., double quotes), not the primary delimiter itself.
- C. Origin refers to the file's character encoding, which is unrelated to the column delimiter.
- D. Data Type Detection affects how Power Query infers column types, not how it splits columns based on delimiters.
Power Query CSV Delimiter
In Power Query Editor's Text/CSV connector, the 'Delimiter' option specifies the character used to separate columns in a text or CSV file. It is crucial for correct parsing of files using non-standard delimiters like semicolons.
- Defines column separation in text/CSV files.
- Configurable in the source settings of the Text/CSV connector.
- Essential for correctly parsing non-comma delimited files.
- Common delimiters include comma, semicolon, tab, and pipe.
Memory trick: Delimiter cuts the CSV file properly.