Forum Discussion
Issue with subtracting columns
- 4 years ago
If you are trying to add a column to the table with the value in each row of the last column subtracted from the value of each column in the nth row, you could do it like this... Just change the number in curly braces in the NthColumn variable to the column number that should be used.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WslDSUTIE4qLUFCBpqRSrAxEzAuKknNJUFEFjIE4vSk3NQxE1AeKC0qKCHFTFpkCcX5SYlw4VjgUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", type text}, {"Column4", Int64.Type}}), add_difference = Table.AddColumn ( #"Changed Type", "NewColumn", let LastColumn = List.Last ( Table.ColumnNames ( #"Changed Type" ) ), NthColumn = Table.ColumnNames ( #"Changed Type" ){1} in each Record.Field ( _, LastColumn ) - Record.Field ( _, NthColumn ) ) in add_difference
Are you trying to add a column to the table with the difference for each row? Or does your table have only one row? Can you add a snip of your table and an example of the expected result?
- zia_ward4 years agoFrequent Visitor
Yes, I want to add a new column to the table, which is a difference of each row of two columns.
Here is what my data looks like:
And here is an Excel example of what I would like as a result. let's say I want to subtract last two columns, so the new column has the difference.
Thanks.