Power Query | Create Column out of two dates

[Laurent500]

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?

[#2 · Accepted solution]

@Laurent500 

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)

let
    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
        Number.RoundUp(
            (Duration.Days(Date.EndOfMonth([End Date]) - Date.StartOfMonth([Start Date])) +1) / AvgDaysPerMonth,
            0
        ), 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"})
in
    RemovedMonths

[#3]

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.

[#4]

@Laurent500 

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...

[#5]

@Laurent500 

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

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