Forum Discussion

sabin_arsenal's avatar
sabin_arsenal
Icon for Helper I rankHelper I
6 years ago
Solved

Subtracting values between two rows

Hello,    I have following two data columns and would like to subtract index (t) with t-1,  i used the following in add coulmn tool in tables, which works but doesn't work in adding column in query...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi sabin_arsenal ,

     

    Something like this:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY7LDQAhCERb2XDWRFB0txZj/23IwP4u5D0cJs5JhRIxxkErTWKo2lBXMZLyajWqGhF4M2waDXA//OLdaGAn4eN5zvfiNO6/vgsBlGbm+I6X4a7SWhs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [INDEX1 = _t, Source = _t, Expected = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"INDEX1", Int64.Type}, {"Source", Int64.Type}, {"Expected", Int64.Type}}),
    mTable = #"Changed Type",
    TableFormat = Value.Type(Table.AddColumn(mTable, "Shifted", each null, type number)),
    mList = List.RemoveLastN({null} & mTable[Source],1),
    Combine = Table.FromColumns (Table.ToColumns(mTable) & {mList}, TableFormat),
    #"Added Custom" = Table.AddColumn(Combine, "Output", each [Source] - [Shifted], type number),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Shifted"})
in
    #"Removed Columns"

     

    Kind regards,

    JB

  • AlB's avatar
    AlB
    6 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Rc25DcAwDEPRXVi78CFZySyC918jlFmke/ggwEx0NIyO0xKj6JeTnKqLXKpGmuika7DJUI2/PuRWfauaLurOA+d8", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Index = _t, Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Column1", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "x(t) - x(t-1) val", each if [Index] <> 0 then  [Column1] - #"Changed Type"[Column1]{[Index]-1} else null)
in
    #"Added Custom"

    Please mark the question solved when done and consider giving kudos if posts are helpful.

     Cheers 

     

sabin_arsenal's avatar
sabin_arsenal
Icon for Helper I rankHelper I
6 years ago

query editor, pls. 

 

and thanks for the suggestion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi sabin_arsenal ,

 

Something like this:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY7LDQAhCERb2XDWRFB0txZj/23IwP4u5D0cJs5JhRIxxkErTWKo2lBXMZLyajWqGhF4M2waDXA//OLdaGAn4eN5zvfiNO6/vgsBlGbm+I6X4a7SWhs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [INDEX1 = _t, Source = _t, Expected = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"INDEX1", Int64.Type}, {"Source", Int64.Type}, {"Expected", Int64.Type}}),
    mTable = #"Changed Type",
    TableFormat = Value.Type(Table.AddColumn(mTable, "Shifted", each null, type number)),
    mList = List.RemoveLastN({null} & mTable[Source],1),
    Combine = Table.FromColumns (Table.ToColumns(mTable) & {mList}, TableFormat),
    #"Added Custom" = Table.AddColumn(Combine, "Output", each [Source] - [Shifted], type number),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Shifted"})
in
    #"Removed Columns"

 

Kind regards,

JB

  • sabin_arsenal's avatar
    sabin_arsenal
    Icon for Helper I rankHelper I
    6 years ago

    thanks, that's way over my head, but that code is pointing to a json file source?

     

    is there an easier way perform this function?

     

    anyone?

    • alena2k's avatar
      alena2k
      Icon for Resolver IV rankResolver IV
      6 years ago

      hi! try to copy/paste complete code using Advanced Editor (rigth mouse click on query)

      JSON contains your data, you will see steps after that.

       

      Here is my suggestion:

       

      let
fnGetDiff = (tbl as table, idx as number) =>
    let
        #"Filtered Rows" = Table.SelectRows(tbl, each [INDEX1] = idx-1 or [INDEX1] = idx),
        Diff = Record.Field( #"Filtered Rows" {1},"Column1.bid.h") - Record.Field( #"Filtered Rows" {0},"Column1.bid.h") 
    in 
        if idx - 1 <  0 then null else Diff,


    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY7LDQAhCERb2XDWRFB0txZj/23IwP4u5D0cJs5JhRIxxkErTWKo2lBXMZLyajWqGhF4M2waDXA//OLdaGAn4eN5zvfiNO6/vgsBlGbm+I6X4a7SWhs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [INDEX1 = _t, Column1.bid.h = _t, #"expected value " = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"INDEX1", Int64.Type}, {"Column1.bid.h", Int64.Type}, {"expected value ", Int64.Type}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "diff", each fnGetDiff( #"Changed Type", [#"INDEX1"]))
in
    #"Invoked Custom Function"