Forum Discussion
zia_ward
4 years agoFrequent Visitor
Issue with subtracting columns
Hi, I have gotten to the following step in my Query and I am not sure why the following step is not working. My goal is find the difference of the last column and column 30th before the last colu...
- 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
jennratten
4 years agoSuper User
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
zia_ward
4 years agoFrequent Visitor
Perfect. Thank you so much. Worked like a charm!!
- jennratten4 years agoSuper User
You're welcome!!