In Power Query based on contract dates assign number of days per month

[Wishmaster40]

Dear All,

I previously made a post, for the same issue but in Excel. See the link below:

Validate month based on contract dates & Calculate number of weeks in that month to calculate hours booked per month

Now i would like to achieve the same but in Power Query, from a API i get the following data back.

And i want to create additional columns in Power Query for the number of days per month.

In Excel this was achieve by formula's not created by myself Felix and 2 other persons, and i really appreciate there help a lot!!

Formula1

Formula2

Formula3

How can i convert one of these formula from above into M code for Power Query?

Of course i understand that i have to create a column for Jan/24, Feb/24 etc and most likely hard code each month into the column or somebody her should know a better solution.

Who can help me out with this?

Many thanks!!

 

[Automatrix]

Luckily, getting the number of days in a month is pretty simple with the Date.DaysInMonth function. Have a look here: Date.DaysInMonth - PowerQuery M

 

[Wishmaster40]

Luckily, getting the number of days in a month is pretty simple with the Date.DaysInMonth function. Have a look here: Date.DaysInMonth - PowerQuery M

Thank you for your reply, but i wasn't able to reproduce the same as in Excel with just this formula.

This video, is very helpful however i get a table of all the dates and i just want an extra column.

List.Generate(
()=> [fDate = pStart, fDay= Date.DaysInMonth(fDate)-Date.Day(fDate)+1],
each [fDate] <= pEnd,
each
let EoM = Date.EndOfMonth( Date.AddMonths([fDate],1)) in
if EoM > pEnd then
[fDate= Date.AddDays(Date.EndOfMonth([fDate]), Date.Day(pEnd)) , fDay= Date.Day(fDate)]
else
[fDate= EoM , fDay = Date.Day(fDate)]

This is what i am looking for just per column the days.