Prorate charges: Splitting a row into multiple rows based on the number of days

Hello, Anonymous your calculations seem strange - why don't you count start date? You show 26 days in Decemeber (as 31 - 5) but what if your start date is Dec 31st? Anyway, suppose you have a table "charges" with columns "start", "end" and "amount". Suppose also that start date should be counted. Then this code

let
    Source = Excel.CurrentWorkbook(){[Name="charges"]}[Content],
    c_types = Table.TransformColumnTypes(Source,{{"start", type date}, {"end", type date}, {"amount", type number}}),
    f = (s as date, e as date, v as number) => 
        let 
            dur = Duration.Days(e - s) + 1, 
            d_cost = v / dur, 
            lst = List.Generate(
                () => [som = Date.StartOfMonth(s), eom = List.Min({Date.EndOfMonth(s), e}), d = Duration.Days(eom - s) + 1],
                (x) => x[som] <= e,
                (x) => [som = Date.AddMonths(x[som], 1), eom = List.Min({Date.EndOfMonth(som), e}), d = Duration.Days(eom - som) + 1],
                (x) => Record.SelectFields(x, {"som", "d"}, null)
            ),
            out = List.Transform(lst, each Record.TransformFields(_, {{"som", Date.MonthName}, {"d",  (x) => x * d_cost}}))
        in out,
    values_col = Table.AddColumn(c_types, "value", (x) => f(x[start], x[end], x[amount])),
    exp_list = Table.ExpandListColumn(values_col, "value"),
    exp_rec = Table.ExpandRecordColumn(exp_list, "value", {"som", "d"}, {"month", "prorated"})
in
    exp_rec

 generates the following table. You may use it as a source for further formatting (e.g. in pivot table) 

Hey all, 

this is the simplest way I found so far. The only missing part is that it does not consider contracts starting and ending in the same month but that can be fixed easily.

https://excelfort.com/allocate-amount-monthly-excel-powerquery/

thanks,
Samer