Forum Discussion
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...
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
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
query editor, pls.
and thanks for the suggestion
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
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?
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
