Issue with subtracting columns

[zia_ward]

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 column.

(note: last step here is called #"Changed Type 1")

= let LastValue = Table.ColumnCount(#"Changed Type1")-1, FirstValue = Table.ColumnCount(#"Changed Type1")-30 in Table.ColumnNames(#"Changed Type1"){LastValue} - Table.ColumnNames(#"Changed Type1"){FirstValue}

The above step basically returns Column names which are dates, instead of subtracting the values of the two columns.

Here is the error message:

Expression.Error: We cannot apply operator - to types Text and Text.
Details:
Operator=-
Left=9/1/21
Right=8/3/21

Thanks.

Zia

[jennratten · Accepted solution]

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]

Perfect. Thank you so much. Worked like a charm!!

[jennratten]

You're welcome!!

[jennratten]

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_ward]

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.