Excel Power Query - 6 Digit YEAR WEEK / MONTH (M Formulas Included)

In my first Excel Power Query Tips video I'm going to show you how to use Power Query's M formula language to create two custom columns YEAR_MONTH and YEAR_WEEK that extracts the Year/Week and Year/Month of a Date field and ensures a consistent number of 6 digits.

So for example Year 2022 Week 6 will be shown as 202206 not 20226.

Below are the full Excel Power Query formulas for the custom columns (change the OrderDate to the name of your Date field):

YEAR_MONTH:
------------------------
Number.ToText(Date.Year([OrderDate])) & Text.End(Number.ToText(0) & Number.ToText(Date.Month([OrderDate])),2)

YEAR_WEEK:
--------------------
Number.ToText(Date.Year ([OrderDate])) &
Text.End(Number.ToText(0) &
Number.ToText(Date.WeekOfYear([OrderDate],Day. Monday)),2)

Enjoy!