List.Durations

List.Durations

D

The M Code Behind the Power Query M function List.Durations

One of the commonly used M functions in Power Query is List.Durations. This function is used to calculate the duration between two dates and times. In this article, we will explore the M code behind the List.Durations function.

Understanding List.Durations Function

Before we dive into the M code behind the List.Durations function, let’s first understand how it works. The List.Durations function takes two lists of dates and times as input. It then calculates the duration between each pair of dates and times in the lists and returns a list of durations.

Here is the syntax of the List.Durations function:


List.Durations(startList as list, endList as list, optional durationUnits as nullable any) as list


The startList and endList are the input lists of dates and times. The durationUnits parameter is optional and specifies the units of the duration returned by the function. If the durationUnits parameter is not specified, the function returns the duration in days.

Now that we have a basic understanding of the List.Durations function let's look at the M code behind it.

The M Code Behind the List.Durations Function

The M code behind the List.Durations function is a combination of several M functions that work together to calculate the duration between two given dates and times.

Here is an example of the M code behind the List.Durations function:


let

startList = {#datetime(2021, 1, 1, 0, 0, 0), #datetime(2021, 1, 2, 0, 0, 0), #datetime(2021, 1, 3, 0, 0, 0)},

endList = {#datetime(2021, 1, 4, 0, 0, 0), #datetime(2021, 1, 5, 0, 0, 0), #datetime(2021, 1, 6, 0, 0, 0)},

durationList = List.Transform(List.Zip({startList, endList}), each Duration.From([{1},{0}] – [{0},{1}]))

in

durationList


The M code above creates two lists, startList and endList, containing the start and end dates and times. It then uses the List.Zip function to combine the two lists into a single list of pairs.

Next, it uses the List.Transform function to apply a transformation to each pair of dates and times in the list. The transformation uses the Duration.From function to calculate the duration between the two dates and times in each pair.

Finally, the M code returns the durationList containing the duration between each pair of dates and times in the input lists.

The List.Durations function is a powerful M function in Power Query that allows users to calculate the duration between two dates and times. Understanding the M code behind the List.Durations function can help users customize the function to suit their specific needs.

In this article, we explored the M code behind the List.Durations function and discussed how it works. We also looked at an example of the M code behind the function.

Using the List.Durations function in Power Query can help users save time and effort in calculating durations between two dates and times.

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)

Copyright, G Com Solutions Ltd, 2024.
Tower Bridge Business Centre, 46-48 East Smithfield, London E1W 1AW
0800 998 9248   |   9:00 a.m. till 5:30 p.m.

Connect With Me: