Subtracting values between two rows

[sabin_arsenal]

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!

INDEX1	Column1.bid.h	expected value
0	10
1	15	5
2	20	5
3	35	15
4	45	10
5	50	5
6	75	25
7	50	-25
8	65	15
9	54	-11
10	57	3

[Anonymous · Accepted solution]

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]

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]

query editor, pls. 

and thanks for the suggestion

[AlB · Accepted solution]

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 

[Anonymous · Accepted solution]

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]

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]

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"