Breakdown of the Power Query M Code

This code snippet is written in Power Query M language, which is commonly used for data manipulation within Microsoft Power BI, Excel, and other Microsoft products. The overall purpose of this code is to create a date table with various date-related columns based on a set of predefined random dates.

Code Structure and Explanation

1. Initialization of Random Dates

RandomDates = { #date ( 2021, 1, 1 ), #date ( 2018, 3, 6 ), #date ( 2022, 12, 30 ) },

• Purpose: Initializes a list of random date values to use as a source for generating a date table.
• #date(year, month, day): A function that creates a date value from the specified year, month, and day.

2. Input Table Date Column

DateColumnInputTable = InputTable[Date],

• Purpose: Specifies a date column from an input table for reference. In practice, you replace InputTable[Date] with the actual table and date column you intend to use.

3. Unique Dates Source

Source = List.Distinct ( RandomDates ),

• Purpose: Removes any duplicate dates from the RandomDates list to ensure each date is unique.

4. Generate Date Range List

DateList =
    let
        MinDate       = List.Min ( Source ),
        MaxDate       = List.Max ( Source ),
        StartDate     = Number.From ( #date ( Date.Year ( MinDate ), 1, 1 ) ),
        EndDate       = Number.From ( #date ( Date.Year ( MaxDate ), 12, 31 ) ),
        Calendar      = { StartDate .. EndDate },
        ToTable       = Table.FromList ( Calendar, Splitter.SplitByNothing(), null, null, ExtraValues.Error ),
        ToDate        = Table.TransformColumnTypes ( ToTable, { "Column1", type date } ),
        ChangeColName = Table.RenameColumns ( ToDate, { { "Column1", "Date" } } )
    in
        ChangeColName,

• MinDate & MaxDate: Determine the earliest and latest dates from the Source.
• StartDate & EndDate: Create numbers representing the first day of the year for MinDate and the last day of the year for MaxDate.
• Calendar: Generates a list of numbers representing the full date range from StartDate to EndDate.
• ToTable: Converts the list of dates into a table format.
• ToDate: Changes the type of the table's column to the date type.
• ChangeColName: Renames the first column to "Date".

5. Adding Additional Date-Related Columns

The subsequent lines of code systematically add various date-related columns to the DateList table:

Month Name

MonthName = Table.AddColumn ( DateList, "Month Name", each Date.MonthName ( [Date] ), type text ),

• Adds a column that contains the name of each month.

Month Number

MonthNumber = Table.AddColumn ( MonthName, "Month Number", each Date.Month ( [Date] ), Int64.Type ),

• Adds a column specifying the numeric representation of each month.

Calendar Year

CalendarYear = Table.AddColumn ( MonthNumber, "Calendar Year", each Date.Year ( [Date] ), Int64.Type ),

• Adds a column indicating the year of each date.

Day Name

DayName = Table.AddColumn ( CalendarYear, "Day of Week Name", each Date.DayOfWeekName ( [Date] ), type text ),

• Adds a column with the name of the day of the week.

Day Number

DayNumber = Table.AddColumn ( DayName, "Day of Week Number", each Date.Day ( [Date] ), Int64.Type ),

• Adds a column that represents the day's position in the month.

Month Short Name

MonthShortName = Table.AddColumn ( DayNumber, "Month Initials", each Text.Start ( Date.MonthName ( [Date] ), 3 ), type text ),

• Adds a column containing the first three letters of the month's name.

Quarter Name

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

• Adds a column denoting the quarter of the year, prefixed with "Q".

Quarter Number

QuarterNumber = Table.AddColumn ( QuarterName, "Quarter Number", each Date.QuarterOfYear ( [Date] ), Int64.Type )

• Adds a column that indicates the quarter number of the year.

6. Final Output

in
    QuarterNumber

• Purpose: Specifies that the final output of the code snippet is the table QuarterNumber, which now contains all the computed date-related columns.

Conclusion

This Power Query M code builds a comprehensive date table capable of supporting time-based analysis for reports and dashboards. It generates a unique list of dates from a set input, calculates various date features, and populates these features into a structured format for analysis.

For further exploration of data manipulation with Power Query, consider utilizing the resources available at the Enterprise DNA Platform to enhance your skills in data science and business intelligence.