Hi @ambepat,
First some impotant things to be aware of when using this forum.
This forum is full of volunteers that give up their own free time to help others, this includes the Expert team. If you single out a specific user it can keep others from providing support - as a result it will take longer to get a response… And finally as this is not an on demand forum condider hiring a payed professional for time sensitive issues.
With that said, I hope you are well and its great to hear you’ve been enjoying the new content 
Please review this session:
Copy the code below into a new blank query.
I’ve made some assumptions that you’ll have to review and possibly amend. It seems to be an annual calculation, so I’ve grouped on “Employee” and “Fiscal Year”. There was no calculation behind the initial value, referred to as “PY Cont.” so I’ve set this value to 0.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7ZhPa8JAEMW/inhOILubv+eWHgr9AEU8eCj0IBGEHvrtu4mGxj8vsjPP2wiamJgfb2bYtzNuNuv3w3e/ej18rbO1K+u6q+NJcHmT+8IX8bz/2e/j4e1zuOD+L8zujYdtBlktjVXkHVGXK4jCnGMq80zY1aNilo+FZLGirqBmhXbMUcx+OT1anEHO+/O38Z0V1XDXN2WbdUtlJIFcXulAU2hq0H9oNUeRGnRlMgxQy8pRx1J08haGJOdomjyNNIC8FjT5yfCouwDFT19mdXw1ydoCG3k2hTnS+6BUWbGR52XJVdnwkS0/8I6v8rR8uTKde4LOmwVEYA7IIEMWwyufg8crD11j2vHvE13+sTv+rt53/YBsKj3RnzS+HPr+cIy3K4LK+3HPTY4Tt4aI4tYwYb0Dvd5i4kK9xUwQ92y7mIinBSSOW0GEcSuYsN4VOW4FEdamptdGTIR5bOh5FBOhxpauUUyEte7otRYT8Z5Y0BMpR8JMuptNUZ1KORLn8mYL0+dSjFzuggJVpJS43LHwNGqIy90FNY9i4kJ3Aea80tX12EuPzbR4DwcjH4UOpz8SHQyCpMyAmZCkHY2HJPFoUmSpB0MjC39/fmTQ50byFO1BR5//W700TF7+7iEIT2eJIDzupILg/JCuCDT56YpAJ56uCLTL6SDQ06aHBhrPdEWoOUyXhBo4gSbQZAlI93dw4XJTg3BPIQzNnMScxJzEnMScxJzEnMScxJzEnMScxJzEnMScxJzEnMScxJzEnMScxJwkEbT9Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, Salary = _t, MonthEndDate = _t, FTE = _t, #"Fiscal Year" = _t, #"Salary Per Month" = _t, #"Employer CPP" = _t, #"Employer CPP Percent" = _t, #"Employer CPP Cap" = _t]),
ChType = Table.TransformColumnTypes(Source,{{"Salary", type number}, {"MonthEndDate", type date}, {"FTE", type number}, {"Salary Per Month", type number}, {"Employer CPP", type number}, {"Employer CPP Percent", type number}, {"Employer CPP Cap", type number}}),
GroupRows = Table.Group(ChType, {"Employee", "Fiscal Year"},
{
{"AllRows", each Table.AddIndexColumn(
Table.Sort( _, {{"MonthEndDate", Order.Ascending}}) // reinforce sort order
, "i", 0, 1 ),
type table [Employee=nullable text, Salary=nullable number, MonthEndDate=nullable date, FTE=nullable number, Fiscal Year=nullable text, Salary Per Month=nullable number, Employer CPP=nullable number, Employer CPP Percent=nullable number, Employer CPP Cap=nullable number, i=nullable number]
}
} ),
ListGenerate = Table.AddColumn(GroupRows, "Employer CPP Costs", each
let
t = [AllRows],
MaxIndex = List.Max( t[i]),
lSalery = List.Buffer( t[Salary Per Month] ),
lCap = List.Buffer( t[Employer CPP Cap] ),
lCPPperc = List.Buffer( t[Employer CPP Percent] ),
Generate = List.Generate(
()=> [
n = 0,
lValues = {0}, // set initial value = "PY Cont."
Calc = try if List.Sum(lValues) >= lCap{n} then 0 else
if lSalery{n} * lCPPperc{n} + List.Sum(lValues) > lCap{n} then lCap{n} - List.Sum(lValues) else
lSalery{n} * lCPPperc{n} otherwise 0
],
each [n] <= MaxIndex,
each [
n = [n]+1,
lValues = [lValues] & { [Calc] },
Calc = try if List.Sum(lValues) >= lCap{n} then 0 else
if lSalery{n} * lCPPperc{n} + List.Sum(lValues) > lCap{n} then lCap{n} - List.Sum(lValues) else
lSalery{n} * lCPPperc{n} otherwise 0
],
each [Calc]
)
in
Generate
),
ExpandAllRows = Table.ExpandTableColumn(ListGenerate, "AllRows", {"Salary", "MonthEndDate", "FTE", "Salary Per Month", "Employer CPP", "Employer CPP Percent", "Employer CPP Cap", "i"}, {"Salary", "MonthEndDate", "FTE", "Salary Per Month", "Employer CPP", "Employer CPP Percent", "Employer CPP Cap", "i"}),
GetValue = Table.RemoveColumns( Table.ReplaceValue(ExpandAllRows, each [Employer CPP Costs], each [Employer CPP Costs]{[i]},Replacer.ReplaceValue,{"Employer CPP Costs"}), {"i"} )
in
GetValue
.
Finally I couldn’t match the “what I’m trying to achieve” sheet results because those value combinations don’t seem to be present. However with the pattern above and after reviewing the training content - I’m confident you can make this work.
I hope this is helpful.
