Power Query | Create Column out of two dates

New Contributor

I have differents projects and each has budget, a start date and an end date.


The mission is to create month columns with the respective month name (or number) and distribute the budget on theses columns equally. Every row should be a project.


I am actually strugling with the creation of the columns. Anyone an idea?


4 Replies
best response confirmed by Laurent500 (New Contributor)


The real challenge in your scenario is to calc. the #Months between [Start Date] and [End Date] as PQ currently doesn't have a function like Duration.Months or an equivalent to Excel/Dax DATEDIFF. I'm not aware of a perfect solution to this (One on the Net "claims" he figured it out but there are situations where his function doesn't return the correct #Months) ==> In the below query adjust AvgDaysPerMonth at your convenience


With List.Accumulate in attached sample:



(can be shortened)

    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangedTypes = Table.TransformColumnTypes(Source,
        {{"Project", type any}, {"Budget", type number}, {"Start Date", type date}, {"End Date", type date}}
    AvgDaysPerMonth = 365/12,
    CalcMonths = Table.AddColumn(ChangedTypes, "Months", each
            (Duration.Days(Date.EndOfMonth([End Date]) - Date.StartOfMonth([Start Date])) +1) / AvgDaysPerMonth,
        ), Int64.Type
    MaxMonths = List.Max(CalcMonths[Months]),
    EarliestStart = Date.StartOfMonth(List.Min(CalcMonths[Start Date])),
    MonthsList = List.Transform({0..MaxMonths}, each Date.AddMonths(EarliestStart, _)),
    AddedMonths = List.Accumulate(MonthsList, CalcMonths,
        (accum,current) as table => Table.AddColumn(accum, Date.ToText(current,"MMM yyyy"), each
            if (Date.StartOfMonth([Start Date]) <= current) and ([End Date] >= current)
            then [Budget] / [Months]
            else null,
            type nullable number
    RemovedMonths = Table.RemoveColumns(AddedMonths,{"Months"})



This looks really good to me.


I did not know List.Accumulate. In the meantime I kind of solved my problem through complicated if then formulas which I obviously did not like at all.


The table you send was of great help. Your solution will expand my horizont for further challenges too.


Thank you very much for your help.


Glad I could help & Thanks for providing feedback. FYI I edited my post this morning

There was a little unecessary calc. Please re-download the sample & Nice day...


Re-edited my post. My bad, forgot to do +1 on the Duration.Days calc.

Please re-re-download & sincere apologies for the inconvenience