Forum Discussion
Solved
Model calculation
Hello all, Would you be so kind and help me out? I am working on selling scenario where campaign is for 5 years. (In attached example it is two years Mar-21 to Feb-23) There is given int...
Hi AndrejZitnay ,
No problem, found the issue. In Advanced Editor, make this change in your Intake query:
//Change this: addPayMonth = Table.AddColumn(expandModelA, "payMonth", each Date.AddMonths([intakeMonth], [payMonthNumber])), //to this: addPayMonth = Table.AddColumn(expandModelA, "payMonth", each Date.AddMonths([Month], [payMonthNumber])), //[payMonth] was being calculated from [intakeMonth], but should actually be calculated from [Month]Optional extra:
Update your modelA query with the following code to remove zero values and have a sharper looking table:
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"}), repZeroNull = Table.ReplaceValue(remWeight,0,null,Replacer.ReplaceValue,{"Mth 1", "Mth 2", "Mth 3", "Mth 4", "Mth 5", "Mth 6", "Mth 7", "Mth 8", "Mth 9", "Mth 10", "Mth 11", "Mth 12", "Mth 13", "Mth 14", "Mth 15", "Mth 16", "Mth 17", "Mth 18"}), unpivotOtherCols = Table.UnpivotOtherColumns(repZeroNull, {"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 chgAllTypesThis gives me:
Pete
Hi AndrejZitnay ,
No problem, found the issue. In Advanced Editor, make this change in your Intake query:
//Change this:
addPayMonth = Table.AddColumn(expandModelA, "payMonth", each Date.AddMonths([intakeMonth], [payMonthNumber])),
//to this:
addPayMonth = Table.AddColumn(expandModelA, "payMonth", each Date.AddMonths([Month], [payMonthNumber])),
//[payMonth] was being calculated from [intakeMonth], but should actually be calculated from [Month]
Optional extra:
Update your modelA query with the following code to remove zero values and have a sharper looking table:
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"}),
repZeroNull = Table.ReplaceValue(remWeight,0,null,Replacer.ReplaceValue,{"Mth 1", "Mth 2", "Mth 3", "Mth 4", "Mth 5", "Mth 6", "Mth 7", "Mth 8", "Mth 9", "Mth 10", "Mth 11", "Mth 12", "Mth 13", "Mth 14", "Mth 15", "Mth 16", "Mth 17", "Mth 18"}),
unpivotOtherCols = Table.UnpivotOtherColumns(repZeroNull, {"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
This gives me:
Pete
