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
Hi mail2rohitnagar
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.