CODEWITHSUNDEEP
powerbipowerquery
Apple
Apple

Reputation: 23

How to generate a list of first day of each month between the two dates in power query

in power query I'm trying to generate list of rows depending on the existing columns StartOfDate and EndOfDate. I want to add a New Column which has a list of dates like below table.

StartOfDate EndOfDate New Column
2023/1/1 2023/2/15 List
2023/2/3 2023/4/2 List

After expanding the list, I only need the first day of months between the StartOfDate and EndOfDate.

StartOfDate EndOfDate New Column
2023/1/1 2023/2/15 2023/1/1
2023/1/1 2023/2/15 2023/2/1
2023/2/15 2023/4/2 2023/2/1
2023/2/15 2023/4/2 2023/3/1
2023/2/15 2023/4/2 2023/4/1

I know I can just generate a list by {Number.From([StartOfDate])..Number.From([EndOfDate])} and then get all the dates between the two date by expanding the list.

But I only need the first day of each month between the dates...

And, since there are already many rows in the table, I do not want to create a step in the query to expand lists of all the dates.

Does anyone know how to to this? Thank you in advance.

Upvotes: 2

Views: 4025

Answers (2)

JvdV
JvdV

Reputation: 75920

Just for fun, an alternative using List.Generate():

=let s=Date.StartOfMonth([StartOfDate]), e=[EndOfDate] in List.Generate(()=>s, each _<=e, each Date.AddMonths(_,1))

Upvotes: 3

horseyride
horseyride

Reputation: 21373

Add column, custom column,

 =List.Distinct(List.Transform({Number.From([StartOfDate])..Number.From([EndOfDate])}, each Date.StartOfMonth(Date.From(_))))

then expand the column into rows using the arrows atop the new column

Upvotes: 4

Related Questions