Mastering Date Transformations in Power Query: A Beginner’s Guide to M Code

Transforming and analyzing date-related data is an essential skill when working with Power Query in Excel. Whether you're creating custom columns to extract the name of the month, the day of the week, or performing other date-related transformations, mastering M Code can take your data analysis to the next level. But don’t worry if you’re new to Power Query — we’ll simplify everything for you!

In this blog post, we’ll dive deep into the world of date transformations using Power Query. Starting with the basics, we’ll walk you through how to extract specific details, such as abbreviations for months and days, and explore other useful transformations to make your data more meaningful. With easy-to-understand explanations, step-by-step M Code examples, and practical use cases, this guide will help you unlock the full potential of date data in Power Query.

By the end of this blog, you'll not only understand how to manipulate date columns effortlessly but also gain confidence in writing M Code to achieve customized transformations. Let’s get started and make date transformations in Power Query as easy as a few clicks!

1. Extract the Year from the Date

This transformation creates a new column that extracts the year from the [Date] column.

M Code:

= Table.AddColumn(#"PreviousStepName", "Year", each Date.Year([Date]), type number)

Output Example:

Date	Year
2023-05-12	2023
2023-06-15	2023
2024-01-01	2024

2. Extract the Month Number

This transformation creates a new column that extracts the numeric value of the month (e.g., 1 for January, 2 for February).

M Code:

= Table.AddColumn(#"PreviousStepName", "Month_Number", each Date.Month([Date]), type number)

Output Example:

Date	Month_Number
2023-05-12	5
2023-06-15	6
2024-01-01	1

3. Extract the Week Number

This transformation creates a column to show the week number of the year for each date.

M Code:

= Table.AddColumn(#"PreviousStepName", "Week_Number", each Date.WeekOfYear([Date]), type number)

Output Example:

Date	Week_Number
2023-05-12	19
2023-06-15	24
2024-01-01	1

4. Extract the Day Number

This transformation creates a new column showing the day number of the month for each date.

M Code:

= Table.AddColumn(#"PreviousStepName", "Day_Number", each Date.Day([Date]), type number)

Output Example:

Date	Day_Number
2023-05-12	12
2023-06-15	15
2024-01-01	1

5. Is the Date a Weekend?

This transformation creates a column to indicate whether the date falls on a weekend (Saturday or Sunday).

M Code:

= Table.AddColumn(#"PreviousStepName", "Is_Weekend", each Date.DayOfWeek([Date], Day.Sunday) >= 5, type logical)

Output Example:

Date	Is_Weekend
2023-05-12	false
2023-05-13	true
2023-05-14	true

6. Find the Quarter of the Year

This transformation creates a column showing the quarter of the year (e.g., Q1, Q2, Q3, Q4).

M Code:

= Table.AddColumn(#"PreviousStepName", "Quarter", each "Q" & Text.From(Date.QuarterOfYear([Date])), type text)

Output Example:

Date	Quarter
2023-05-12	Q2
2023-06-15	Q2
2024-01-01	Q1

7. Day of the Year

This transformation calculates the day number of the year for each date (e.g., January 1 is day 1, December 31 is day 365 or 366).

M Code:

= Table.AddColumn(#"PreviousStepName", "Day_Of_Year", each Date.DayOfYear([Date]), type number)

Output Example:

Date	Day_Of_Year
2023-05-12	132
2023-06-15	166
2024-01-01	1

8. Add Custom Date Formats

This transformation creates a column that formats the date in a specific way (e.g., MM/dd/yyyy).

M Code:

= Table.AddColumn(#"PreviousStepName", "Custom_Date_Format", each Text.PadStart(Text.From(Date.Month([Date])), 2, "0") & "/" & Text.PadStart(Text.From(Date.Day([Date])), 2, "0") & "/" & Text.From(Date.Year([Date])), type text)

Output Example:

Date	Custom_Date_Format
2023-05-12	05/12/2023
2023-06-15	06/15/2023
2024-01-01	01/01/2024

9. First Day of the Month

This transformation calculates the first day of the month for each date.

M Code:

= Table.AddColumn(#"PreviousStepName", "First_Day_Of_Month", each Date.StartOfMonth([Date]), type date)

Output Example:

Date	First_Day_Of_Month
2023-05-12	2023-05-01
2023-06-15	2023-06-01
2024-01-01	2024-01-01

10. Last Day of the Month

This transformation calculates the last day of the month for each date.

M Code:

= Table.AddColumn(#"PreviousStepName", "Last_Day_Of_Month", each Date.EndOfMonth([Date]), type date)

Output Example:

Date	Last_Day_Of_Month
2023-05-12	2023-05-31
2023-06-15	2023-06-30
2024-01-01	2024-01-31

11. Weekday Number (0 = Sunday, 6 = Saturday)

This transformation creates a column showing the day of the week as a number (0 for Sunday, 1 for Monday, etc.).

M Code:

= Table.AddColumn(#"PreviousStepName", "Weekday_Number", each Date.DayOfWeek([Date], Day.Sunday), type number)

Output Example:

Date	Weekday_Number
2023-05-12	5
2023-05-13	6
2023-05-14	0

12. Custom Weekday Labels

This transformation allows you to create a custom weekday label for each date.

M Code:

= Table.AddColumn(#"PreviousStepName", "Custom_Weekday_Label", each if Date.DayOfWeek([Date], Day.Sunday) = 0 then "Weekend" else "Weekday", type text)

Output Example:

Date	Custom_Weekday_Label
2023-05-12	Weekday
2023-05-13	Weekend
2023-05-14	Weekend

Some Advanced M Code functions related to Date Transformations:

To extract the Name of the Month (e.g., Jan from the date 2023-01-15) from the date in the given example, you can use the Date.MonthName function in Power Query (M code). Below is the M code to accomplish this:

Date Month
2023-01-15 Jan
2023-02-20 Feb
2023-03-10 Mar

M Code:

= Table.AddColumn(#"Added Custom", "Month", each Text.Start(Date.MonthName([Date]),3), type text)

Detailed Parsing:

1 ) = Table.AddColumn(

Meaning: This is a function in Power Query (M language) that adds a new column to an existing table.
Action: It specifies that you are adding a new column to the table from the previous step.

2) #"Added Custom"

Meaning: This is the name of the table from the previous step in your query. In Power Query, steps are often referenced by their names.
Action: The function will add the new column to this table. This is a reference to an existing table.

3) "Month",

Meaning: This is the name of the new column that will be added to the table.
Action: A new column named "Month" will be created.

4) each

Meaning: This is a keyword in Power Query that is used to apply a function to each row of the table.
Action: It signals that the operation defined next should be applied to each row in the table.

5) Text.Start(

Meaning: This is a function that returns a specified number of characters from the start of a text string.
Action: The function will extract the first few characters from the result of Date.MonthName.

6) Date.MonthName([Date])

Meaning: The Date.MonthName function returns the full name of the month (e.g., "January", "February") for the value in the [Date] column.
Action: For the given row's [Date] value, this extracts the name of the month.

7) [Date]

Meaning: This is a reference to the column named "Date" in the current row.
Action: The function takes the value in the [Date] column of the current row and uses it as input for Date.MonthName.

8) 3),

Meaning: This specifies the number of characters to extract from the text returned by Date.MonthName. For example, "January" becomes "Jan".
Action: Only the first 3 characters of the month name are extracted.

9) type text

Meaning: This specifies the data type of the new column being added. In this case, the column will contain text values.
Action: Ensures that the new column "Month" is of the text data type.

Full Explanation in Plain English:
Step 1: The function starts by referencing the table from the previous step (called #"Added Custom").
Step 2: A new column named "Month" will be added to this table.
Step 3: For each row in the table:
The Date.MonthName([Date]) function retrieves the full name of the month (e.g., "January", "February") from the value in the [Date] column.
The Text.Start function then extracts the first 3 characters of the month name (e.g., "Jan" from "January").
Step 4: The new column is defined as text using type text.

Improved and Commented M Code:

Here’s the same M code with inline comments (in Blue) for clarity:

= Table.AddColumn(#"Changed Type",/* Reference to the previous step/table in the query */

"Custom", /* Name of the new column to be added */

each Text.Start( /* Apply the following function to each row */

Date.MonthName([Date]), /* Get the full month name from the "Date" column */

3 /* Extract only the first 3 characters (e.g., "Jan" from "January") */

), type text)

M Code Made Easier for a Layman:

Here’s how this code works step by step in simple terms:

Imagine you have a table with dates in one column (e.g., "2023-01-15").
You want to add a new column to this table that shows the first three letters of the month (e.g., "Jan").
The M Code:= Table.AddColumn(#"Added Custom", "Month", each Text.Start(Date.MonthName([Date]),3), type text):

Looks at each row in the table.
Takes the date from the [Date] column.
Converts the date into the full month name (e.g., "January").
Extracts the first three letters (e.g., "Jan").
Adds this result as a new column called "Month".
Finally, it ensures that the new column contains text values.

What You’ll See in Power Query:

After applying this code, the table will look something like this:

Date	Month
2023-01-15	Jan
2023-02-20	Feb
2023-03-10	Mar

To extract the day number (e.g., 15 from 2023-01-15) from the date in the previous example, you can use the Date.Day function in Power Query (M code). Below is the M code to accomplish this:

M Code:

= Table.AddColumn(
    #"Added Custom", // Reference to the previous step/table in the query
    "Day", // Name of the new column to be added
    each Date.Day([Date]), // Extract the day number from the "Date" column
    Int64.Type // Specify the data type as an integer
)

Explanation:

Table.AddColumn:
- This function adds a new column to the table.
#"Added Custom":
- This is the name of the table from the previous step in the query.
"Day":
- The name of the new column that will contain the extracted day numbers.
each Date.Day([Date]):
- The Date.Day function retrieves the day number (e.g., 15 from 2023-01-15) from the [Date] column for each row.
Int64.Type:
- This sets the data type of the new column to a 64-bit integer, which is suitable for day numbers.

Result:

After applying this code, your table will look like this:

Date	Day
2023-01-15	15
2023-02-20	20
2023-03-10	10

For a Layman:

This code will:

Add a new column to your table.
Look at the [Date] column for each row.
Extract the day number from the date (e.g., 15 for January 15, 2023).
Add this number as a new column called "Day".

Let's break down the provided M Code step by step, add commentary to guide you, and provide an easy-to-understand explanation for what it does:

M Code:

= Table.AddColumn(#"Added Custom", "Day_Of_Week", each Text.Start(Date.DayOfWeekName([Date]),3), type text)

Step-by-Step Parsing and Commentary:

= Table.AddColumn:
- This is a function in Power Query used to add a new column to an existing table.
- The new column will be calculated based on the logic provided in the code.
#"Added Custom":
- This is a reference to the previous step in your query. In Power Query, every step is named (either automatically or manually by you).
- In this case, "Added Custom" refers to the name of the table/output from the previous step.
"Day_Of_Week":
- This is the name of the new column being created.
- The new column will store the abbreviated names of the days of the week (e.g., "Mon", "Tue", "Wed").
each:
- This is a keyword in Power Query that is used to apply the subsequent logic to each row in the table.
- It essentially means "perform the following operation on every row in the table."
Date.DayOfWeekName([Date]):
- This is a Power Query function that retrieves the full name of the day of the week for the value in the [Date] column.
- For example:
  - If [Date] is 2023-05-12, this function will return "Friday".
  - If [Date] is 2023-05-13, this function will return "Saturday".
Text.Start(..., 3):
- The Text.Start function takes the result of Date.DayOfWeekName (e.g., "Friday") and extracts the first 3 characters.
- For example:
  - "Friday" becomes "Fri".
  - "Saturday" becomes "Sat".
- This is how you get the abbreviated day names in the new column.
type text:
- This specifies that the data type of the new column should be text.
- This ensures that the values in the "Day_Of_Week" column are stored as text (e.g., "Mon", "Tue").

What Does This Code Do?

The code creates a new column called "Day_Of_Week".
For each row in the table:
1. It looks at the value in the [Date] column.
2. It calculates the full day name (e.g., "Monday" or "Tuesday") using the Date.DayOfWeekName function.
3. It extracts the first 3 characters of the day name (e.g., "Mon", "Tue") using the Text.Start function.
4. The result is stored in the new column as text.

Example:

If your table initially looks like this:

Date
2023-05-12
2023-05-13
2023-05-14

After applying the M Code, it will look like this:

Date	Day_Of_Week
2023-05-12	Fri
2023-05-13	Sat
2023-05-14	Sun

For Beginners:

This code will:

Create a new column called "Day_Of_Week".
For each row, look at the date in the [Date] column.
Figure out the day of the week (e.g., "Monday" or "Friday") for the date.
Take the first 3 letters of the day (e.g., "Mon" or "Fri").
Save this value as text in the new column.

Notes:

Make sure the [Date] column in your table contains valid date values.
If the [Date] column contains invalid data (e.g., text or null values), you may encounter errors.

Smart Learning

Friday, December 27, 2024

Mastering-power-query-m-code-a-beginners-guide-to-Learning-Date-Transformations

Mastering Date Transformations in Power Query: A Beginner’s Guide to M Code

1. Extract the Year from the Date

M Code:

Output Example:

2. Extract the Month Number

M Code:

Output Example:

3. Extract the Week Number

M Code:

Output Example:

4. Extract the Day Number

M Code:

Output Example:

5. Is the Date a Weekend?

M Code:

Output Example:

6. Find the Quarter of the Year

M Code:

Output Example:

7. Day of the Year

M Code:

Output Example:

8. Add Custom Date Formats

M Code:

Output Example:

9. First Day of the Month

M Code:

Output Example:

10. Last Day of the Month

M Code:

Output Example:

11. Weekday Number (0 = Sunday, 6 = Saturday)

M Code:

Output Example:

12. Custom Weekday Labels

M Code:

Output Example:

Some Advanced M Code functions related to Date Transformations:

DateMonth2023-01-15Jan2023-02-20Feb2023-03-10Mar

M Code:

Detailed Parsing:

Improved and Commented M Code:

M Code Made Easier for a Layman:

What You’ll See in Power Query:

M Code:

Explanation:

Result:

For a Layman:

M Code:

Step-by-Step Parsing and Commentary:

What Does This Code Do?

Example:

For Beginners:

Notes:

Related Article

Smart Learning

Date Month
2023-01-15 Jan
2023-02-20 Feb
2023-03-10 Mar