Расчет общей суммы платежа между периодами дат с разной периодичностью выплат

В Beginner тег относится к Power-Query скорее, чем Excel.


Для этой проблемы я изначально написал код VBA, который отлично работал. С тех пор мне было поручено интегрировать полученные результаты с моделью данных электронных таблиц, поэтому я подумал, что PowerQuery подойдет для этого лучше. Я думаю, мой M-код показывает, что нельзя просто переводить с одного языка на другой.
Если вас интересует сторона VBA, начальный вопрос: Вот.

Логика кода VBA работает в PowerQuery до тех пор, пока минимальный и максимальный диапазон дат в таблице составляет всего месяц или два. На практике диапазон дат часто составляет двадцать лет и более.
Мой новый код работает, поэтому я спрашиваю здесь, а не о SO, но чувствую, что мне, возможно, придется взглянуть на проблему под совершенно другим углом.

Деталь

Моя исходная таблица названа Необработанные данные:
введите описание изображения здесь

И это таблица результатов:
введите описание изображения здесь

Результат показывает:

  • С 1 января по 20 января производился ежеквартальный платеж. 30 х 4 = 120 в год.
  • Между 15 января и 17 января производился минусовый ежемесячный платеж. -5 х 12 = -60 в год.
    Эта стоимость добавляется к существующим 120 в год, поэтому в итоговой таблице показано 60 за этот период.
  • Был произведен единовременный платеж за период с 5 по 10 января. 6/6 = 1 * 365 = 365.
    Это добавляется к 120 за год, поэтому в итоговой таблице показано 485 за этот период.

Код запроса ниже.
Я вижу, что должно быть огромное узкое место при объединении таблиц и при вычислении результата за день, но я недостаточно знаю язык, чтобы думать о другом способе получения результата.

let

    Source = Excel.CurrentWorkbook(){[Name="RawData"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"Component", type text}, {"Type", type text}, 
                                                     {"Start", type date}, {"End", type date}, 
                                                     {"Amount", type number}, {"Frequency", type text}}),
    FormatFrequency = Table.TransformColumns(ChangedType,{{"Frequency", Text.Proper, type text}}),
    DailyValue = Table.AddColumn(FormatFrequency, "DayAmount", each if [Frequency]="Quarterly" then ([Amount]*4)/365 
                                                           else if [Frequency]= "Monthly" then ([Amount]*12)/365 
                                                           else if [Frequency]="One-Time" then [Amount]/(Duration.Days(Date.From([End])-Date.From([Start]))+1)
                                                           else 0),
    
    //Get list of all dates between minimum & maximum date in table.
    StartDate = List.Min(DailyValue[Start]),
    EndDate = List.Max(DailyValue[End]),

    //I am hoping to have multiple components and group the final table by these as well as dates.
    //Currently each row in the date list is given the first component value so I can group when summing the columns.
    //Ideally I'd like to have the ListDates table contain a component along with each date that represents it.
    Comp = List.First(DailyValue[Component]),
    ListDates = Table.FromList(List.Dates(StartDate,Number.From(EndDate-StartDate)+1,#duration(1,0,0,0)), Splitter.SplitByNothing(),null,null,ExtraValues.Error),
    AddComponent = Table.AddColumn(ListDates, "Component", each Comp),
    RenameColumns2 = Table.RenameColumns(AddComponent,{{"Column1","Dates"}}),
    ReorderedColumns = Table.ReorderColumns(RenameColumns2,{"Component", "Dates"}),

    //Merge the two tables based on the component and identify which rows from the first table will be relevant to each date in the second.
    MergeTables = Table.NestedJoin(ReorderedColumns, {"Component"}, DailyValue, {"Component"}, "DataTable", JoinKind.LeftOuter),
    ExpandTable = Table.ExpandTableColumn(MergeTables, "DataTable", {"Start", "End", "DayAmount"}, {"Start", "End", "DayAmount"}),
    AddAmountToDate = Table.AddColumn(ExpandTable, "Daily", each if [Dates]>=[Start] and [Dates]<=[End] then [DayAmount] else null),

    //Tidy the table, sum any daily values that fall on the same day and multiply by 365 to get annual value.
    RemoveNulls = Table.SelectRows(AddAmountToDate, each [Daily] <> null),
    RemoveColumns = Table.RemoveColumns(RemoveNulls,{"Start", "End", "Daily"}),
    SumValues = Table.Group(RemoveColumns, {"Component", "Dates"}, {{"Total", each List.Sum([DayAmount]), type number}}),
    MakeAnnual = Table.TransformColumns(SumValues, {{"Total", each _ * 365, type number}}),
    
    //Identify where annual value changes and record start/end dates.
    AddIndex  = Table.AddIndexColumn(MakeAnnual, "Index", 0, 1),
    IsStart = Table.AddColumn(AddIndex, "Start", each if [Index] = 0 then [Dates]
                                                 else if [Total]<>AddIndex[Total]{[Index]-1} then [Dates]
                                                 else null),
    IsEnd = Table.AddColumn(IsStart, "End", each if [Index] = List.Max(IsStart[Index]) then [Dates]
                                            else if [Total]<>IsStart[Total]{[Index]+1} then [Dates]
                                            else null),
    RemoveEmptyRows = Table.SelectRows(IsEnd, each [Start] <> null or [End] <> null),
    RemovedIndex = Table.RemoveColumns(RemoveEmptyRows,{"Index"}),
    NewIndex = Table.AddIndexColumn(RemovedIndex, "Index", 0, 1),

    FinalEnd = Table.AddColumn(NewIndex, "Final End", each if [Start] <> null and [End] <> null then [End]
                                                      else if [End] = null then NewIndex[End]{[Index]+1} 
                                                      else null),
    RemoveNullFinal = Table.SelectRows(FinalEnd, each ([Final End] <> null)),
    TidyUp = Table.SelectColumns(RemoveNullFinal,{"Component", "Start", "Final End", "Total"}),
    RenameColumn = Table.RenameColumns(TidyUp,{{"Final End", "End"}})
in
    RenameColumn

Любая помощь будет принята с благодарностью, чтобы заставить это работать с приемлемой скоростью.

0

Добавить комментарий

Ваш адрес email не будет опубликован.