Date.StartOfMonth

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Date.StartOfMonth

Syntax

The syntax for the Date.StartOfMonth function is as follows:

Date.StartOfMonth(dateTime as any) as any

The function takes a single parameter, dateTime, which can be any valid date/time value. The function returns a date/time value representing the first day of the month for the given dateTime value.

Examples

Here are some examples of how the Date.StartOfMonth function can be used:

Example 1

Suppose you have a table of sales data that includes a column for the date of each sale. You want to group the sales data by month and calculate the total sales for each month. You can use the following code to achieve this:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45WMlTSUXLIyCxKLU0tKMlXKMpNzSspVrJSaiQK”, BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Sales = _t]),

#”Changed Type” = Table.TransformColumnTypes(Source,{{“Date”, type date}, {“Sales”, Int64.Type}}),

#”Grouped Rows” = Table.Group(#”Changed Type”, {“Date.StartOfMonth”}, {{“Total Sales”, each List.Sum([Sales]), type nullable number}})

in

#”Grouped Rows”

In this code, we first create a table from some sample data. We then use the Date.StartOfMonth function to group the data by month. Finally, we calculate the total sales for each month using the List.Sum function.

Example 2

Suppose you have a list of dates and you want to create a new list that contains the first day of each month for those dates. You can use the following code to achieve this:

let

Source = #date(2020, 1, 1),

#”Added Custom” = List.Transform({1..12}, each Date.AddMonths(Source, _)),

#”Added Custom1″ = List.Transform(#”Added Custom”, each Date.StartOfMonth(_))

in

#”Added Custom1″

In this code, we first create a single date value using the #date function. We then use the List.Transform function to create a list of dates representing the first day of each month for the year 2020.

The Date.StartOfMonth function is a useful tool in Power Query for working with datasets that include dates. By returning the first day of each month for a given date, this function allows you to easily group data by month and perform calculations on a monthly basis.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)