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 are following up once again regarding your query. If the issue has been resolved,kindly mark the helpful response and accept it as the solution to assist other community members in resolving similar issues more efficiently. If not, please provide detailed information so we can better assist you.
Thank you for your understanding and participation.