who create a list first day of month and last day of month

[PabloSM]

Good morning, I would like to create a list, for each Item. Put the first day and the last day of each month.

I have a formula like this.

List.Dates(#date(2022,01,01),365*2,#duration(1,0,0,0))

But I need only the 1st and the last one to debug.

Any Help?

Best.

[v-stephen-msft · Accepted solution]

Hi @PabloSM ,

You can create a blank query, and put the below codes into the advanced editor to check.

The whole M codes:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtY11DVUio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StartDate = _t]),
    #"Added Custom" = Table.AddColumn(Source, "EndDate", each Date.FromText("2023-12-31")),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"EndDate", type date}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type2",{{"StartDate", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Dates", each {Number.From([StartDate])..Number.From([EndDate])}),
    #"Expanded Dates" = Table.ExpandListColumn(#"Added Custom1", "Dates"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Dates",{{"Dates", type date}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"StartDate", "EndDate"}),
    #"Added Custom2" = Table.AddColumn(#"Removed Columns1", "Month", each Date.Month([Dates])),
    #"Grouped Rows" = Table.Group(#"Added Custom2", {"Month"}, {{"FirstDate", each List.Min([Dates]), type nullable date}, {"LastDate", each List.Max([Dates]), type nullable date}, {"All Rows", each _, type table [Dates=nullable date, Month=number]}}),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All Rows", {"Dates"}, {"Dates"}),
    #"Added Custom3" = Table.AddColumn(#"Expanded All Rows", "Custom", each if [Dates]=[FirstDate] or [Dates]=[LastDate] then 1 else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom3", each ([Custom] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Month", "FirstDate", "LastDate", "Custom"})
in
    #"Removed Columns"

And you said you want a list, add as new query.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[PabloSM]

good morning,
with these formulas I get the first and the last of a date.

What I need is to create a list with the first and the last. from a date up to 1 or 2 years in the future.

01/01/2022
31/01/2022
01/02/2022
28/02/2022
01/03/2022
31/03/2022
....

Using this formula: 

List.Dates(start as date, count as number, step as duration) as list

Best.

[pratyashasamal]

Hi @PabloSM ,
You can you Date.StartOfMonth for 1st day of the month :-
Date.StartOfMonth(dateTime as any) as any
You can you Date.LastOfMonth for last day of the month :-
Date.EndOfMonth(dateTime as any) as any
To Create a list simply you can create a list using these two columns

Hope this will be helpful.

You can refer to these links:-
https://learn.microsoft.com/en-us/powerquery-m/date-endofmonth
https://learn.microsoft.com/en-us/powerquery-m/date-startofmonth
Thanks ,
Pratyasha Samal
​Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

[PabloSM]

Same result:

01/01/2022
02/01/2022
03/01/2022

....

List.Dates(#date(2022,01,01),Number.From(#date(2023,12,31))-Number.From(#date(2022,01,01))+1,#duration(1,0,0,0))

i need create a list for each line:

01/01/2022
31/01/2022
01/02/2022
28/02/2022
01/03/2022
31/03/2022

...

Best.