Duration.Days

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Duration.Days

Understanding the Duration.Days Function

The Duration.Days function is a simple yet powerful function that calculates the number of days between two given dates. The function takes two arguments, start and end dates, and returns the number of days between them. Here’s the syntax of the Duration.Days function:

Duration.Days(startDate as date, endDate as date) as number

The function takes two date values, start date and end date, and returns a number that represents the number of days between the two dates.

The M Code Behind the Duration.Days Function

Under the hood, the Duration.Days function is implemented using the M language, which is the native language of Power Query. The M language is a functional programming language that is used to define the data transformation logic in Power Query. Let's take a look at the M code behind the Duration.Days function:

(Duration as duration) =>

Duration.Days(Duration)

The code above defines a function that takes a duration value and returns the number of days represented by the duration value. The Duration.Days function is called to calculate the number of days between the start and end dates.

Understanding Duration Values

Before we dive deeper into the M code behind the Duration.Days function, let's take a moment to understand what duration values are. In Power Query, a duration value represents a time span. A duration value is defined using the Duration.FromText function, which takes a text value as input and returns a duration value. Here's the syntax of the Duration.FromText function:

Duration.FromText(text as text) as duration

The function takes a text value that represents a time span and returns a duration value that represents the same time span.

The M Code for Duration Values

The M code for duration values is straightforward. Here's the code that defines a duration value:

Duration.FromText(“1 day”)

The code above defines a duration value that represents a time span of one day. The value can be changed to represent any other time span, such as "2 hours", "30 minutes", or "3 months".

Using the Duration.Days Function in Power Query

Now that we understand the M code behind the Duration.Days function, let's see how we can use it in Power Query. Here's an example of how to use the Duration.Days function to calculate the number of days between two dates:

let

startDate = #date(2021,1,1),

endDate = #date(2021,12,31),

duration = Duration.FromText(“1 day”),

durationList = List.Dates(startDate, Duration.Days(endDate-startDate)+1, duration),

result = Table.FromList(durationList, {“Date”}, null, null, ExtraValues.Error),

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

in

#”Changed Type”

The code above defines a Power Query query that calculates the number of days between two dates using the Duration.Days function. The query first defines the start and end dates, then defines a duration value of one day using the Duration.FromText function. The List.Dates function is called to generate a list of dates between the start and end dates using the duration value. The resulting list is transformed into a table, and the date column is converted to a date type.

The Duration.Days function is a powerful function that is widely used in Power Query to calculate the number of days between two dates. The function is implemented using the M language, which is the native language of Power Query. Understanding the M code behind the Duration.Days function can help data analysts and business intelligence professionals to better understand the function and use it more effectively in their data analysis scenarios.

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)