Microsoft Certified: Fabric Analytics Engineer AssociatePrepare and transform data (20-25%)Easy
A data engineering team is using Dataflows Gen2 to ingest sales data from a legacy CSV file. The file uses a semicolon (`;`) as a delimiter instead of the standard comma. When the engineer attempts to load the CSV file into Dataflows Gen2, all data appears in a single column. Which configuration setting needs to be adjusted in Dataflows Gen2's Power Query Editor to correctly parse the file?
- AAdjust the 'Delimiter' setting in the CSV source connector to semicolon.
- BChange the 'Decimal Separator' to semicolon.
- CModify the 'Culture' setting to a locale that uses semicolons.
- DApply a 'Split Column by Delimiter' transformation after loading.
Show answer & explanationAnswer & explanation
Correct answer: A. Adjust the 'Delimiter' setting in the CSV source connector to semicolon.
When ingesting a CSV file with a non-standard delimiter, the 'Delimiter' setting in the source connector (within Power Query Editor) must be explicitly set to match the file's delimiter. If not, Power Query will default to comma and treat the entire row as a single column.
Why the other options are wrong
- B. Decimal separator affects how numbers are parsed (e.g., 1.5 vs 1,5), not how columns are separated.
- C. Culture settings influence data formats (dates, numbers) but do not override the explicit column delimiter for CSV files.
- D. Applying 'Split Column by Delimiter' *after* loading would work, but it's less efficient and less ideal than configuring the delimiter at the source to ensure correct parsing from the start.
Power Query CSV Delimiter
A setting in Power Query's CSV source connector that specifies the character used to separate fields (columns) within a CSV file.
- Defaults to comma (`,`).
- Must be configured for non-standard delimiters (e.g., semicolon, tab).
- Incorrect setting leads to data appearing in a single column.
Memory trick: The delimiter is the 'road sign' telling Power Query where columns divide.