Forum Discussion

mail2rohitnagar's avatar
mail2rohitnagar
Regular Visitor
1 year ago
Solved

Need help with calculated column

I have a usecase to calculate corrected target based on the previous month variance. Below is my scenario   Column Name Values(M1) Values(M2) Values(M3) Sales Person Name John John Jo...
  • v-karpurapud's avatar
    v-karpurapud
    1 year ago

    Hi 

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1djbbtowAAbgV0G5hsgH4oTLduukIjFQe9GLqqooyzYkaKoUOu1mF3urvc6eZHYI1A7GJwXHk1pyahM+nIQ///19NIz6EQIooZO75Wq1nK/p3KwsvmwXm1c6Oy6+P9PJZLkoi9fi66YxP8WE/evf3396OAbZfhb0M4z7GNN1gK4DMaxeMZtgQLfFGLMDJ78QoVP6c/FSDqp3wQ74yHb7COAADAf1m2O7hXGa9T6UdKm3PyC3COixdoswxqA3oOvYrh/691ESCHIy/ylDJjzynaFGVhtqJNsyjHFWYUkg2PH2WYYlIhYTEyyKU8RjSQzTCpsGg13JsCmPxYYjCxojm8QZrLBZINiL7TcZNmvjNMb7kR0Fgr3NX2TYURvYw2kMQSDa6WIj0ULQrhYGov1cvMm0sF0tCkT7MV/ItKhd7Q5LusaO5/TbhxxdttXQkrawKBDsp/xJhkWtYnEg2Mm8lGFxq1guFt8UT3nJBLd5+bZc5AbWWfEjLy+vD28GpvUs7BNC6G+61+LqFbDjJKBadzITz6Z3VzeX18exuIFlhzuRK+gG4auW7qIRj7vDitmYx6risTGWbWnG4+6wYjbmsaQdLL1oGvG4S+zqBDZtFZuFgBWzMY9txmNoiCVS7CgErJiNeawqHtuMLDqKx91pxWzMaZXx2P485uNxd1oxG/NaVTx20KIQtGI25rWqeOygjQ4hqsMbspCN+cu2GY+xIXYoxaIQsGI25rGqeGwzsqAZj7sMUeUJrCoe25/Gutb4ar2eK7TcswA6e2sMxNYYmbTGuz88jsedYk3aYxRnwARbfzDofQmb9se+uA79sWJsxf6YfUxG/bE/rHV/rBnbkTC2NVfXIHu7bu0b5JNcdNQg1096ugbZF9ahQTbGDunzgVmD7Evr0CA7aHUNsi+tQ4PsoNU1yL60Dg2ygzZS94zebsn2DbI9Vtcg+8I6NMj2WF2D7C9IWTfI9lhNg6yx/h8Nsiwid4c9e4NcP/xoGmQ/2EAaZF/YIBpkT9dsGA2yH2woDbIfbSgNsh9tKA2yH61hgwwNtbulDPNLZh2yp1vyeTtkuH+I13TIfrCBdMi+YpSHDvnhHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [MONTH = _t, YEAR = _t, #"PRINCIPAL MANAGERS" = _t, #"PROD/ SERVICE" = _t, #"SALES PERSON" = _t, PRINCIPAL = _t, PRATICULARS = _t, REFERENCE = _t, #"REV, RS. LAKHS" = _t, REV = _t, #"MARGIN %" = _t, #"MARGIN (RS. LAKHS)" = _t, MARGIN = _t, #"Financial Year" = _t, #"BUD. / ACT." = _t, #"MONTH YEAR" = _t, #"Unique Id" = _t, #"Actual Sales" = _t, #"Target in Cr" = _t, #"Target Margin in Cr" = _t, Variance = _t, CorrectedTarget = _t]),
    
        
        Clean1 = Table.ReplaceValue(Source, "₹", "", Replacer.ReplaceText, {"Target in Cr", "Actual Sales"}),
        Clean2 = Table.ReplaceValue(Clean1, "Cr", "", Replacer.ReplaceText, {"Target in Cr", "Actual Sales"}),
    
      
        ChangedTypes = Table.TransformColumnTypes(Clean2, {
            {"MONTH", Int64.Type}, {"YEAR", Int64.Type},
            {"Target in Cr", type number}, {"Actual Sales", type number}
        }),
    
        
        #"Removed Old CT" = Table.RemoveColumns(ChangedTypes, {"CorrectedTarget"}),
    
        
        Sorted = Table.Sort(#"Removed Old CT", {
            {"SALES PERSON", Order.Ascending},
            {"PRATICULARS", Order.Ascending},
            {"YEAR", Order.Ascending},
            {"MONTH", Order.Ascending}
        }),
    
      
        Columns = Table.ColumnNames(Sorted),
        Rows = Table.ToRows(Sorted),
    
        AddCorrectedTarget = List.Accumulate(
            Rows,
            { {}, 0, null, null },  
            (state, currentRow) =>
                let
                    result = state{0},
                    prevCT = state{1},
                    prevSP = state{2},
                    prevPart = state{3},
    
                    salesPerson = currentRow{List.PositionOf(Columns, "SALES PERSON")},
                    particular = currentRow{List.PositionOf(Columns, "PRATICULARS")},
                    target = try Number.From(currentRow{List.PositionOf(Columns, "Target in Cr")}) otherwise 0,
                    actual = try Number.From(currentRow{List.PositionOf(Columns, "Actual Sales")}) otherwise 0,
    
                    isFirst = List.Count(result) = 0 or salesPerson <> prevSP or particular <> prevPart,
                    newCT = if isFirst then target else prevCT - actual + target,
    
                    rowWithCT = Record.AddField(Record.FromList(currentRow, Columns), "CorrectedTarget", newCT),
                    updatedRows = List.Combine({result, {rowWithCT}})
                in
                    {updatedRows, newCT, salesPerson, particular}
        ){0},
    
        FinalTable = Table.FromRecords(AddCorrectedTarget)
    in
        FinalTable


    Regards,
    Karpurapu D.