Microsoft Certified: Power BI Data Analyst AssociatePrepare the dataMedium
A data analyst is preparing sales data for a Power BI report. The data contains a 'SalesDate' column in text format (e.g., '2023-01-15 14:30:00'). The analyst needs to extract only the year from this column to analyze annual sales trends. Which Power Query transformation step should be applied to achieve this efficiently?
- AUse a custom column with M function `Date.Year([SalesDate])` directly.
- BSplit Column by Delimiter, then remove time part.
- CChange Data Type to Date, then Add Column > Date > Year.
- DExtract > Text Before Delimiter with delimiter ' ' (space).
Show answer & explanationAnswer & explanation
Correct answer: C. Change Data Type to Date, then Add Column > Date > Year.
To correctly extract the year, the column should first be converted to a proper Date or DateTime data type. Once it's a date type, Power Query's built-in 'Add Column > Date > Year' transformation can accurately extract the year component without manual text manipulation, which is less robust.
Why the other options are wrong
- A. While `Date.Year` is the correct M function, directly applying it to a text column would result in an error; the column must first be converted to a date/datetime type. The 'Add Column > Date > Year' automatically handles this conversion under the hood if the source is convertible.
- B. Splitting by delimiter and removing parts is less efficient and prone to errors compared to proper date type conversion and extraction.
- D. Extracting text before a space would give '2023-01-15', which is not just the year.
Extracting Date Parts
The process of isolating specific components (like year, month, day) from a date or datetime column in Power Query for analytical purposes.
- Requires the column to be of a Date or DateTime data type.
- Power Query provides built-in transformations for common date parts.
- Ensures accurate extraction, unlike text manipulation.
Memory trick: Date must be a date, then extract the part you crave.