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

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

     

     

5 Replies

  • jennratten's avatar
    jennratten
    Super User
    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's avatar
    jennratten
    Super User
    4 years ago

    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's avatar
      zia_ward
      Frequent Visitor
      4 years ago

      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.