Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
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
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
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.