Forum Discussion
List.Tranform formula help
Hello M language wizards,
I'll start by saying my M is pretty basic, and thus your help will be much appreciated!
Need: create a list from one date to another, but only with the 1st of each month.
E.g.: from 01-Jan-2022 to 30-May-2023, it would be 01-Jan-2022, 01-Feb-2022, ... , 01-May-2023.
What my colleague managed to come up with: generating a list with EVERY date in that period.
= Table.AddColumn(#"Custom", "Date_in_subscription", each
List.Transform( { Number.From ( [Subscriptions.Start Date] ) ..Number.From ( [Subscriptions.End Date] ) }, each Date.From (_) ))
Question: is there a way to change this formula to only include the 1st of the month?
Huge thanks in advance,
Alice
You could try
= Table.AddColumn(#"Custom", "Date_in_subscription", each List.Distinct(List.Transform( { Number.From ( [Subscriptions.Start Date] ) ..Number.From ( [Subscriptions.End Date] ) }, each Date.StartOfMonth(Date.From (_)) )) )
2 Replies
You could try
= Table.AddColumn(#"Custom", "Date_in_subscription", each List.Distinct(List.Transform( { Number.From ( [Subscriptions.Start Date] ) ..Number.From ( [Subscriptions.End Date] ) }, each Date.StartOfMonth(Date.From (_)) )) )John, you are a prince among men! Thank you so much! It works!
