Microsoft Certified: Fabric Analytics Engineer AssociatePrepare and transform data (20-25%)Medium
A data engineer is working on a Dataflow Gen2 to ingest sales data from an Azure SQL Database. One of the columns, `OrderDate`, is stored as a `VARCHAR` in the source database but contains valid date strings (e.g., '2023-10-26'). For analytical purposes in the Lakehouse, this column must be converted to a proper `Date` data type to enable date-based filtering and calculations. Which Power Query transformation should the engineer apply to the `OrderDate` column to achieve this conversion reliably?
- AChange Type
- BExtract Text
- CReplace Values
- DMerge Columns
Show answer & explanationAnswer & explanation
Correct answer: A. Change Type
The 'Change Type' transformation in Power Query is specifically designed to convert a column from one data type to another. For `VARCHAR` columns containing date strings, selecting 'Date' as the target type will parse and convert the string values into proper date objects, enabling date-specific operations and ensuring data quality downstream.
Why the other options are wrong
- B. 'Extract Text' is used to pull out specific parts of a text string (e.g., first 5 characters), not to convert its entire data type.
- C. 'Replace Values' is used to find and replace specific text values within a column, not to change its fundamental data type.
- D. 'Merge Columns' combines multiple columns into a single column, which is not the goal here.
Power Query Type Conversion
A Power Query transformation that changes the data type of a column, essential for ensuring data quality and enabling correct operations on the data.
- Accessed via 'Change Type' option in Power Query Editor.
- Supports various conversions (Text to Number, Text to Date, etc.).
- Crucial for data quality and downstream analytical capabilities.
- Errors if conversion is not possible (e.g., 'abc' to Number).
Memory trick: Change Type is like a data chameleon, transforming its nature.