Microsoft Certified: Power BI Data Analyst AssociatePrepare the dataEasy
A data analyst is working with a sales dataset in Power Query. The dataset includes a 'SalesDate' column, which is currently of a 'Date' data type. The analyst needs to create a new column that indicates the 'Year' of each sale for trend analysis. Which Power Query transformation should be used to extract the year from the 'SalesDate' column?
- AAdd Custom Column using `Date.Year()`
- BSplit Column by Delimiter ('-') and select the first part
- CExtract Text Before Delimiter ('-') twice
- DDuplicate Column, then Transform > Date > Year
Show answer & explanationAnswer & explanation
Correct answer: D. Duplicate Column, then Transform > Date > Year
Duplicating the 'SalesDate' column and then applying the built-in 'Year' transformation from the 'Date' menu is the most direct and user-friendly way to extract the year. This preserves the original date column while creating a new one for the year.
Why the other options are wrong
- A. While `Date.Year()` in a custom column would work, the built-in transformation under 'Date' is often simpler and more discoverable for this common task.
- B. Splitting by delimiter would create text columns, requiring additional type conversion, and is less direct than using the built-in 'Year' transformation for a 'Date' type.
- C. This approach treats the date as text, which is less robust and more error-prone than using dedicated date functions for a 'Date' data type.
Extracting Date Parts (Power Query)
Power Query transformations that allow extracting specific components (e.g., Year, Month, Day, Week of Year) from a column with a 'Date' or 'DateTime' data type into new, separate columns.
- Applies to Date or DateTime data types.
- Found under 'Transform' tab > 'Date' group.
- Common extractions include Year, Month, Day, Day of Week, Week of Year.
Memory trick: Dates are like LEGOs; you can pull out just the 'Year' block.