Transform start and end date to a list of months to be expanded

[Kassie72]

Hi,

I would like to get a list of months between start and end date on a row and expand it with the number months in between.

I am using the following table sample:

Employee	StartDate	EndDate
John	01/01/2023	13/12/2024
Theresa	15/03/2024	31/12/2025

All days within the start date are considered to count for a full month. So I have used a formula to determine the first day of the month: Date.StartOfMonth([StartDate]). This works fine.

Then I have made a calculation of the months in between the StartDate and the EndDate: MonthsInBetween
(Date.Year([EndDate]) - Date.Year([StartDate])) * 12 + (Date.Month([EndDate]) - Date.Month([StartDate])). This works fine.

Now I would like to make a list to expand the StartDate with the MonthsInBetween. This is what does not work.
List.Transform(
List.Numbers(0, [MonthsInBetween] + 1),
each Date.AddMonths(Date.StartOfMonth([StartDate]), _)
)

Generating the values seem to work fine (a green line below the column header), but when I expand I get Errors in the field. When double clicking on the error message, I get the following:
Expression.Error: Cannot apply field access to the type Number.
Details:
Value=0
Key=StartDate

Can someone help me out here?

[SundarRaj]

Hi @Kassie72 , here's a solution you could take a look at. Thanks

[slorin]

https://learn.microsoft.com/en-us/powerquery-m/m-spec-functions#simplified-declarations

each _ + 1 
<=>
(_) => _ + 1 


each [A]
<=>
(_) => _[A] 

then

each 
List.Transform(
List.Numbers(0, [MonthsInBetween] + 1),
each Date.AddMonths(Date.StartOfMonth([StartDate]), _)
)

is equivalent to

(_) => 
List.Transform(
List.Numbers(0, _[MonthsInBetween] + 1),
(_) => Date.AddMonths(Date.StartOfMonth(_[StartDate]), _)
)

You must distinguish between two _ 

Stéphane

[Kassie72]

Yes it works, thanks.
Could you briefly elaborate the diffence between your and my solution?

[slorin · Accepted solution]

Hi @Kassie72 

(x) => List.Transform(
List.Numbers(0, x[MonthsInBetween] + 1),
each Date.AddMonths(Date.StartOfMonth(x[StartDate]), _)
)

or

List.Transform(
List.Numbers(0, [MonthsInBetween] + 1),
(x) => Date.AddMonths(Date.StartOfMonth([StartDate]), x)
) 

Stéphane