Microsoft Certified: Power BI Data Analyst AssociatePrepare the dataMedium
A data modeler is preparing a fact table in Power Query from a transactional system. The table contains a 'SalesDateTime' column. For reporting purposes, the modeler needs to create a separate 'Date' column containing only the date part (without time) and a 'Time' column containing only the time part (without date). These new columns should be derived from 'SalesDateTime' and added to the table. Which Power Query transformation approach is most suitable?
- AAdd Custom Column using `Date.From([SalesDateTime])` and another Custom Column using `Time.From([SalesDateTime])`.
- BSplit Column by Delimiter (space), then change type for each new column.
- CDuplicate 'SalesDateTime' twice, then for one duplicate change type to 'Date', and for the other, change type to 'Time'.
- DUse 'Add Column > Date > Date Only' and 'Add Column > Time > Time Only'.
Show answer & explanationAnswer & explanation
Correct answer: D. Use 'Add Column > Date > Date Only' and 'Add Column > Time > Time Only'.
Power Query offers direct, user-friendly options under 'Add Column' to extract 'Date Only' and 'Time Only' components from a DateTime column. This is the most straightforward and efficient method compared to manual duplication, custom M functions, or splitting text.
Why the other options are wrong
- A. While `Date.From` and `Time.From` are correct M functions, the 'Add Column' options provide a GUI-based, simpler way to achieve the same result without writing custom M code.
- B. Splitting by space would work if the data was consistently formatted as 'YYYY-MM-DD HH:MM:SS', but it's less robust than using built-in date/time functions and would require extra type conversion steps.
- C. Duplicating and changing type will convert the entire column to either date or time, not extract components into separate columns.
Extract Date/Time Components
The process in Power Query of creating new columns that isolate either the date part or the time part from an existing DateTime column.
- Requires the source column to be of DateTime data type.
- Power Query has dedicated UI options for this under 'Add Column'.
- Ensures precise separation of date and time components.
- Results in new columns without modifying the original.
Memory trick: From DateTime, 'Add Column' makes Date and Time fly.