Microsoft Certified: Fabric Analytics Engineer AssociatePrepare and transform data (20-25%)Easy
A data engineer is using a Dataflow Gen2 to ingest sales data from an Azure SQL Database. The sales data table includes a 'SaleDate' column of type `datetimeoffset`. The destination in the Lakehouse is a Delta table where the corresponding column should be stored as `DATE` (without time or offset information). Which Power Query transformation is the most direct and efficient to achieve this data type conversion?
- AUsing 'Extract' -> 'Date Only' from the 'SaleDate' column and then changing the type to 'Date'.
- BSplitting the column by delimiter ' ' (space) and then taking the first part.
- CApplying a custom M expression `Date.From([SaleDate])` and then changing the column type.
- DChanging the column data type directly to 'Date' in the column header menu.
Show answer & explanationAnswer & explanation
Correct answer: D. Changing the column data type directly to 'Date' in the column header menu.
Power Query's type conversion capabilities are robust. When converting a `datetimeoffset` or `datetime` column to a `Date` type directly from the column header menu, Power Query automatically handles the extraction of the date component and discards the time and offset information, making it the most direct and efficient method.
Why the other options are wrong
- A. While 'Extract' -> 'Date Only' works, simply changing the type directly is often a single, more streamlined step in Power Query.
- B. Splitting by delimiter is a less robust and less efficient method for converting datetime to date, especially if the format can vary, and it doesn't handle the `datetimeoffset` type correctly by default.
- C. Writing a custom M expression is valid but is less direct and often unnecessary when a built-in UI option achieves the same result more simply.
Power Query Type Conversion to Date
In Power Query Editor, converting a `datetime` or `datetimeoffset` column to a `Date` type can be directly achieved by selecting 'Change Type' from the column header. Power Query automatically extracts the date component, discarding time and offset.
- Directly available from column header context menu.
- Automatically handles extraction of date part.
- Efficient and straightforward for common conversions.
- Works for `datetime` and `datetimeoffset` sources.
Memory trick: Change column type to date directly for simplicity.