Model calculation

Hi AndrejZitnay ,

Here are the queries from your sample file you provided. The Intake query has all the transformations in it to get to the correct monthly payments based on the modelA criteria.

You will need to amend the source lines to point to the sample file you sent me.

I've left all the columns in the Intake query to make it easier for you to follow the steps or extract columns earlier in the process for reporting.

The modelA and modelAweight queries will also need to be brought in and called these names as they are referenced in the Intake query for a merge and crossjoin respectively.

Intake

let
    Source = yourSampleFile.xlsx,
    #"Intake _Sheet" = Source{[Item="Intake ",Kind="Sheet"]}[Data],
    promHeads = Table.PromoteHeaders(#"Intake _Sheet", [PromoteAllScalars=true]),
    addModelAweight = Table.AddColumn(promHeads, "modelAweight", each modelAweight),
    expandModelAweight = Table.ExpandTableColumn(addModelAweight, "modelAweight", {"weightMonth", "Weight", "weightMonthNumber"}, {"weightMonth", "Weight", "weightMonthNumber"}),
    addIntakeMonth = Table.AddColumn(expandModelAweight, "intakeMonth", each Date.AddMonths([Month], [weightMonthNumber])),
    addIntakeValue = Table.AddColumn(addIntakeMonth, "intakeValue", each [Intake] * [Weight]),
    mergeModelA = Table.NestedJoin(addIntakeValue, {"weightMonth"}, modelA, {"modelMonth"}, "modelA", JoinKind.LeftOuter),
    expandModelA = Table.ExpandTableColumn(mergeModelA, "modelA", {"payMonthNumber", "paySplit"}, {"payMonthNumber", "paySplit"}),
    addPayMonth = Table.AddColumn(expandModelA, "payMonth", each Date.AddMonths([intakeMonth], [payMonthNumber])),
    addPayValue = Table.AddColumn(addPayMonth, "payValue", each [intakeValue] * [paySplit]),
    chgAllTypes = Table.TransformColumnTypes(addPayValue,{{"Month", type date}, {"Intake", Int64.Type}, {"weightMonth", type text}, {"Weight", type number}, {"intakeMonth", type date}, {"intakeValue", type number}, {"weightMonthNumber", Int64.Type}, {"payMonth", type date}, {"payValue", type number}})
in
    chgAllTypes

modelAweight

let
    Source = yourSampleFile.xlsx,
    #"Model A_Sheet" = Source{[Item="Model A",Kind="Sheet"]}[Data],
    promHeads = Table.PromoteHeaders(#"Model A_Sheet", [PromoteAllScalars=true]),
    remOthCols = Table.SelectColumns(promHeads,{"Month", "Weight"}),
    renCols = Table.RenameColumns(remOthCols,{{"Month", "weightMonth"}}),
    addMonthNumber = Table.AddColumn(renCols, "weightMonthNumber", each Text.AfterDelimiter([weightMonth], " "), type text),
    chgAllTypes = Table.TransformColumnTypes(addMonthNumber,{{"weightMonth", type text}, {"Weight", type number}, {"weightMonthNumber", Int64.Type}})
in
    chgAllTypes

modelA

let
    Source = yourSampleFile.xlsx,
    #"Model A_Sheet" = Source{[Item="Model A",Kind="Sheet"]}[Data],
    promHeads = Table.PromoteHeaders(#"Model A_Sheet", [PromoteAllScalars=true]),
    remWeight = Table.RemoveColumns(promHeads,{"Weight"}),
    unpivotOtherCols = Table.UnpivotOtherColumns(remWeight, {"Month"}, "Attribute", "Value"),
    renCols = Table.RenameColumns(unpivotOtherCols,{{"Attribute", "payMonth"}, {"Value", "paySplit"}, {"Month", "modelMonth"}}),
    addModelMonthNumber = Table.AddColumn(renCols, "payMonthNumber", each Text.AfterDelimiter([payMonth], " "), type text),
    chgAllTypes = Table.TransformColumnTypes(addModelMonthNumber,{{"paySplit", type number}, {"modelMonth", type text}, {"payMonthNumber", Int64.Type}})
in
    chgAllTypes

Summary:

- Crossjoin modelAweight onto Intake table and multiply each original value by weight value.

- Merge unpivoted modelA table onto Intake table on weightMonth>modelMonth fields, then multiply weighted values by payment split values.

- Use month number added to start month on each step to give correct future dates for intake and payment.

Hopefully you will be able to see the steps I've taken on each of the queries to get from your sample file data to the final monthly payment schedule but, if not, let me know and I'll try and help.

Pete