Microsoft Certified: Power BI Data Analyst AssociatePrepare the dataMedium
A data engineer is preparing a large dataset in Power Query that aggregates daily sales figures from a data warehouse. The dataset contains a 'SaleDate' column of type Date. To analyze sales trends by month and quarter, the engineer needs to add new columns for 'Month Number', 'Month Name', and 'Quarter Number'. Which Power Query feature should be used to efficiently create these new columns?
- AExtract Text Before Delimiter
- BAdd Column from Examples
- CAdd Custom Column using M functions
- DDate tools in the Add Column tab
Show answer & explanationAnswer & explanation
Correct answer: D. Date tools in the Add Column tab
Power Query's 'Add Column' tab provides dedicated Date tools (under 'Date & Time' group) that can automatically extract components like 'Month', 'Name of Month', 'Quarter of Year', etc., from a Date column with a few clicks, making it the most efficient method.
Why the other options are wrong
- A. This transformation is for text manipulation, not for extracting date components from a Date type column.
- B. Add Column from Examples is useful for inferring complex transformations, but for standard date parts, the dedicated tools are more direct.
- C. While M functions can achieve this, the built-in Date tools are more efficient and less prone to syntax errors for standard date component extraction.
Date Part Extraction (Power Query)
Power Query provides built-in date tools within the 'Add Column' tab to easily extract various components (e.g., year, month, day, quarter) from a date or datetime column, simplifying the creation of new time-intelligence columns.
- Dedicated tools in 'Add Column' tab.
- Extracts components like Year, Month, Day, Quarter.
- Simplifies time-intelligence column creation.
Memory trick: Dates have parts, Power Query knows them all.