Forum Discussion
mail2rohitnagar
1 year agoRegular Visitor
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...
- 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.
v-karpurapud
1 year agoCommunity Support
We have not yet received a response from you regarding your query if you find the response helpful, kindly mark it as the accepted solution and provide kudos, as this will aid other members with similar queries.
Thank You.