Forum Discussion
calculate new collumn in M-code
- 1 year ago
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:
Hi , i forget to add group by
please refere the latest pbix .
anyway i have created measure also in my previous file generating one order for each. please check that solution too.
attaching power query editor solution too.
Hope this helps.
thanks rajendraongole1 , but I need to keep all the rows and collumns.
In my file I have much more collumns and rows which I need to keep, Ive just provided collumns which are related to my issue.
When I use group by, it will remove other collumns which I dont want to.
Also, I dont want to remove duplicates rows, as I need all the rows.
I literally needs to achieve this state as picture bellow, just dont know how or if its even possible