List.Dates

`startDate`: The starting date for the list.

`numberOfDays`: The number of days to include in the list.

`stepSize` (optional): The number of days between each date in the list. Default is 1.

Here's an example usage of List.Dates:

This would generate a list of 7 dates starting from January 1, 2021: `{#date(2021,1,1), #date(2021,1,2), #date(2021,1,3), #date(2021,1,4), #date(2021,1,5), #date(2021,1,6), #date(2021,1,7)}`.

The M code behind List.Dates

So how does List.Dates actually generate this list of dates? Let's take a closer look at the M code behind the function.

Here's the basic structure of the M code for List.Dates:

The function takes in the `startDate`, `numberOfDays`, and optional `stepSize` parameters. It then uses the `List.Generate` function to create the list of dates.

`List.Generate` is a powerful function that allows for generating a list based on custom logic. Here's the syntax:

`initialGenerator`: A function that generates the first item in the list.

`conditionGenerator`: A function that determines when to stop generating items. The function takes in the current item and returns a boolean indicating whether to continue generating items.

`itemGenerator`: A function that generates the next item in the list based on the previous item.

`selector` (optional): A function that transforms each item in the list. Default is identity function.

In the case of List.Dates, we use `List.Generate` to generate a list of dates starting from `startDate` and continuing until `numberOfDays` days have been generated. The `stepSize` parameter determines the number of days between each date.

Let's break down the three functions passed into `List.Generate` for List.Dates:

`() => startDate`: This is the initial generator function, which simply returns the `startDate` parameter to start the list.

`each _ <= Date.AddDays(startDate, numberOfDays - 1)`: This is the condition generator function, which checks if the current date (`_`) is less than or equal to the end date (`Date.AddDays(startDate, numberOfDays - 1)`). If the condition is true, `List.Generate` continues generating items.

`each Date.AddDays(_, stepSize)`: This is the item generator function, which generates the next date in the list by adding `stepSize` days to the previous date (`_`).

List.Dates is a powerful function in Power Query M for generating lists of dates within a specified range. By understanding the M code behind this function, you can gain a deeper understanding of how it works and how to use it effectively in your data transformations. Next time you need to generate a list of dates in Power Query, give List.Dates a try and see what kind of results you can achieve!

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

Upcoming Courses

Contact Us