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 editor (lookupvalue doesnt come on as function)

 

( [Column1.bid.h]-IF([Index]=0,E[Column1.bid.h],LOOKUPVALUE([Column1.bid.h],[Index],[Index]-1)))

 

any help is greatly appreciated!

 

INDEX1Column1.bid.hexpected value 
010 
1155
2205
33515
44510
5505
67525
750-25
86515
954-11
10573
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 

     

6 Replies

  • 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 

     

      • 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