Forum Discussion
calculate new collumn in M-code
Hi All, I have following table in PBI, in power query. I need to find a way how to fill in Netto column. Netto column should be: Sum of Bill - Sum of PDV per same order So Im expecting only 2 ...
Honestly, we'd have provided an approach for that had you mentioned that you wanted to get just the difference for the first row and not get the sum of difference for each order.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQPMNQ3NFDSUTLSszQGUaZ6pkqxOuTJGCHJGJsBKUMLPQsqyBxaoABGKCpiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Order = _t, PDV = _t, Bill = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order", type text}, {"PDV", type number}, {"Bill", type number}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Order"}, {{"Grouped", each _, type table [Order=nullable text, PDV=nullable number, Bill=nullable number]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Added Index", each Table.AddIndexColumn([Grouped], "Index", 1)), #"Expanded Added Index" = Table.ExpandTableColumn(#"Added Custom", "Added Index", {"PDV", "Bill", "Index"}, {"PDV", "Bill", "Index"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Added Index",{"Grouped"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Index", "Order", "PDV", "Bill"}), #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"PDV", type number}, {"Bill", type number}, {"Index", Int64.Type}}), #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom", each if [Index] = 1 then [Bill] - [PDV] else null) in #"Added Custom1"Please see below:
