Forum Discussion
sabin_arsenal
Helper I
Helper ISubtracting 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...
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
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_arsenalHelper I
Helper Iquery editor, pls.
and thanks for the suggestion
AlB
Community Champion
Community Championlet
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