Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sabin_arsenal
Helper I
Helper I

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
2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

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

View solution in original post

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 

SU18_powerbi_badge

 

View solution in original post

6 REPLIES 6
AlB
Super User
Super User

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 

 

SU18_powerbi_badge

query editor, pls. 

 

and thanks for the suggestion

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 

SU18_powerbi_badge

 

Anonymous
Not applicable

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?

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"

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors