Forum Discussion

zia_ward's avatar
zia_ward
Frequent Visitor
4 years ago
Solved

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...
  • jennratten's avatar
    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