List of months (beginnings of months) from / to in Power Query

In each row there is the period, its beginning and end, both as a date datatype. And you need statistics by month.

E.g. you have a list of contracts with information about the beginning and the end of validity, and you need to find out for each month how many of them were valid in that month.

The easiest way is (usually) to split each row into multiple rows, one for each month covered by this row.

But how to do it?

If we start from this table:

We can use this syntax:

• List.Select(
     List.Dates(
        Date.StartOfMonth([Start]),
        Number.From([End] – [Start]),
        #duration(1, 0, 0, 0)),
     each Date.Day(_) = 1)

Why this?

• List.Dates lists all dates between the start date and the end date. We change the start date to the first so that even the first month is inclued.
• List.Select will only keep the days that are the beginnings of the month

This generates lists, that can be transformed to rows.

Now we have beginnings of months for all the period.