Forum Discussion

sabin_arsenal's avatar
sabin_arsenal
Helper 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 

     

AlB's avatar
AlB
Community Champion
6 years ago

Hi sabin_arsenal 

Do you want to do this in DAX or in the query editor?

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

 Cheers 

 

sabin_arsenal's avatar
sabin_arsenal
Helper 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
      Helper 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
        Resolver 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"

         

  • AlB's avatar
    AlB
    Community Champion
    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