Latest Enterprise DNA Initiatives


I am stuck to convert a formula in excel into m code

Hi @Melissa ,

Long time. Hope you well and happy new year.

I have enjoyed your videos and learning lots and becoming more and more comfortable with power query but still lots to learn.

I am stuck on a problem and it has to do with cummulative calculation and how to achieve this in power query.

I have attached my sample file below.

If you open this file and go to the tab “What I am trying to achieve” you will see the formula in Cell Q8. I am stuck on the cumulative addition per month part of this formula as I don’t know how to achieve this in power query.

If you go to power query in the last step Employer CPP costs in the salary table that is where I have done my m code calculations but quite clearly I don’t know how to include my answer as a cumulative total in the calculation and hence stuck.

Your help will be appreciated and thanks very much in advance.

Patrick N
Cummulative Calc Case Problem.xlsm (96.9 KB)

Hi @Melissa ,

If you have time could you PLEASE look into this. I am so stuck and I don’t know how to solve this.

Thanks

Patrick

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 :+1:
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.

3 Likes

Thank you for this very detailed solution @Melissa :slight_smile:

Hi @ambepat, we’ve noticed that no response has been received from you since a few days ago.

We just want to check if you still need further help with this post?

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

Hi Melissa,

Thanks for your response and I take your comments fully onboard so won’t happen next time.

I will review your solution together with watching the videos to see if I can match those numbers.

Thanks

Patrick N

1 Like

@Melissa glad to say it worked and you’ve done it again. Never cease to amaze me on your power query prowess and knowledge. Not sure if you will add this to your problems solved files for others to learn. Thanks again.

Glad I could help Patrick :+1: