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 raw data contains a column named 'TransactionDateTime' with values like '2023-05-10T14:30:00Z'. For analytical purposes, the modeler needs separate columns for 'TransactionDate' (e.g., '2023-05-10') and 'TransactionTime' (e.g., '14:30:00'). Which Power Query transformation simplifies this extraction process?
- AAdd Custom Column using `Date.FromText()` and `Time.FromText()` functions.
- BDuplicate 'TransactionDateTime', then use 'Date Only' and 'Time Only' transformations.
- CSplit Column by Delimiter (T), then change data types.
- DExtract Text Before Delimiter (T) for date, and Extract Text After Delimiter (T) for time.
Show answer & explanationAnswer & explanation
Correct answer: B. Duplicate 'TransactionDateTime', then use 'Date Only' and 'Time Only' transformations.
Duplicating the column and then using the built-in 'Date Only' and 'Time Only' transformations is the most straightforward and efficient way to separate date and time components from a DateTime column in Power Query.
Why the other options are wrong
- A. While M functions can achieve this, the built-in 'Date Only' and 'Time Only' transformations are simpler to use and more discoverable for this common task.
- C. Splitting by delimiter would create two text columns, requiring additional steps to convert them to proper Date and Time types, which is less direct.
- D. Extracting text before/after a delimiter works on text. For a DateTime type, using the dedicated transformations is more robust and maintains the data type integrity.
Extract Date/Time Components (Power Query)
Power Query transformations that allow separating or extracting specific parts (like Date, Time, Year, Month, Day, Hour, Minute, Second) from a Date/Time column into new columns.
- Applies to columns with Date, Time, or DateTime data types.
- Common options include 'Date Only', 'Time Only', 'Year', 'Month', 'Day'.
- Found under 'Transform' tab > 'Date' or 'Time' sections.
Memory trick: DateTime columns are like combo meals; you can get just the 'Date' or just the 'Time' part.