Forum Discussion
Calculate difference from latest value
Hi guys,
this one is a variation of this post. Since I can't mark it as 'unsolved', it made sense to start a new topic.
In the previous post, I have an example, in which I wanted to subtract two values from different rows when they had a different type (there's a Type column) and referred to the same date. Now I'm going to present a different example because the goal is a little different.
Here's the simplified version of my dataset. The Selection column actually represents a few columns grouped into one.
The table is not necessarily ordered by latest date.
Date Type Value Selection
1/6 A 4 X
1/5 A 2 X
1/4 B 3 X
1/3 A 5 Y
1/3 B 2 Y
1/2 B 3 X
1/1 A 3 Y
1/1 B 1 Y
What I want is, for every row of type A, calculate the difference between its value and the value of the last row of type B, with a Date equal or prior to its Date and of the same Selection. I'll try to show the output I'm expecting:
Date Type Value Selection Difference
1/6 A 4 X 4 - 3 =1 (from row 3)
1/5 A 2 X 2 - 3 = -1 (from row 3)
1/4 B 3 X blank
1/3 A 5 Y 5 - 3 = 2 (from row 6, because row 5 has different Selection value)
1/3 B 2 X blank
1/2 B 3 Y blank
1/1 A 3 Y 3-1 = 2
1/1 B 1 Y blank
How could I approach this? Aditionally, if there's another column with a true/false value saying if you should ignore that row, how would that change my approach? Feel free to message me with questions if something isn't clear enough.
Thanks ! :)
This is one possibility:
let // This function selects the corresponding B-Row for every A value SelectB = (selection, date) => let // several filters following
// omitt this first filter if you don't have the true/false-field #"Filtered Rows3" = Table.SelectRows(ChgType, each ([#"true/false"] = "true")), // without the true/false-field, the next filter will look like this:
// #"Filtered Rows" = Table.SelectRows(ChgType, each ([Selection] = selection)),
#"Filtered Rows" = Table.SelectRows(#"Filtered Rows3", each ([Selection] = selection)), #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Date] <= date), #"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each ([Type] = "B")), // Just keep the latest row from this selection
#"Sorted Rows" = Table.Buffer(Table.Sort(#"Filtered Rows2",{{"Date", Order.Descending}})), #"Kept First Rows" = Table.FirstN(#"Sorted Rows",1) in #"Kept First Rows", // Example data - replace with your table Source1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUMzDTMzIwNFPSUVJwBBImIIZCBJAsKSpNVYrVgSgyRVFkhF2RCVyRE5Awxq7IGMUkU7CiSNyKnODWgRSlJeYUI1QZEWOfIYp9xtjtgysCGWSIpiYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Type = _t, Value = _t, Selection = _t, #"true/false" = _t]), ChgType = Table.Buffer(Table.TransformColumnTypes(Source1,{{"Date", type date}, {"Type", type text}, {"Value", Int64.Type}, {"Selection", type text}})), // Here the function is called #"Added Custom" = Table.AddColumn(ChgType, "BValue", each SelectB([Selection], [Date])), #"Expanded BValue" = Table.ExpandTableColumn(#"Added Custom", "BValue", {"Value"}, {"ValueB"}), #"Added Custom1" = Table.AddColumn(#"Expanded BValue", "Difference", each if [Type]="A" then [Value]-[ValueB] else "") in #"Added Custom1"
12 Replies
This is one possibility:
let // This function selects the corresponding B-Row for every A value SelectB = (selection, date) => let // several filters following
// omitt this first filter if you don't have the true/false-field #"Filtered Rows3" = Table.SelectRows(ChgType, each ([#"true/false"] = "true")), // without the true/false-field, the next filter will look like this:
// #"Filtered Rows" = Table.SelectRows(ChgType, each ([Selection] = selection)),
#"Filtered Rows" = Table.SelectRows(#"Filtered Rows3", each ([Selection] = selection)), #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Date] <= date), #"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each ([Type] = "B")), // Just keep the latest row from this selection
#"Sorted Rows" = Table.Buffer(Table.Sort(#"Filtered Rows2",{{"Date", Order.Descending}})), #"Kept First Rows" = Table.FirstN(#"Sorted Rows",1) in #"Kept First Rows", // Example data - replace with your table Source1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUMzDTMzIwNFPSUVJwBBImIIZCBJAsKSpNVYrVgSgyRVFkhF2RCVyRE5Awxq7IGMUkU7CiSNyKnODWgRSlJeYUI1QZEWOfIYp9xtjtgysCGWSIpiYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Type = _t, Value = _t, Selection = _t, #"true/false" = _t]), ChgType = Table.Buffer(Table.TransformColumnTypes(Source1,{{"Date", type date}, {"Type", type text}, {"Value", Int64.Type}, {"Selection", type text}})), // Here the function is called #"Added Custom" = Table.AddColumn(ChgType, "BValue", each SelectB([Selection], [Date])), #"Expanded BValue" = Table.ExpandTableColumn(#"Added Custom", "BValue", {"Value"}, {"ValueB"}), #"Added Custom1" = Table.AddColumn(#"Expanded BValue", "Difference", each if [Type]="A" then [Value]-[ValueB] else "") in #"Added Custom1"Thanks for taking the time and replying. I'm going to give it a try on Monday and come back with the results. I really appreciate your help!
I believe I managed to adapt this to my code, as per your instructions. I've added a few more filters, since - as I said - selection is not an actual field, but several fields that I had to filter from.
I'm waiting for it to finish, but it seems like it will take several hours to complete, which is worrisome. It's processed 254 rows in 3 minutes, out of 260.000 in my dataset. Here's what my code looks like - I've changed the names to hide sensitive info, but the structure is the same.
let // This function selects the corresponding B-Row for every A value SelectB = (d, e, f, g, date) => let // several filters following
// this is actually the true/false column. It's a custom column that is true if [Date] = [T] #"Filtered Rows2" = Table.SelectRows(Source1, each ([Date] = [T])), #"Filtered Rows4" = Table.SelectRows(#"Filtered Rows2", each ([D] = d)), #"Filtered Rows5" = Table.SelectRows(#"Filtered Rows4", each ([E] = e)), #"Filtered Rows6" = Table.SelectRows(#"Filtered Rows5", each ([F] = f)), #"Filtered Rows" = Table.SelectRows(#"Filtered Rows3", each ([G] = g)), #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Date] <= date), #"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each ([Type] = "B")), // Just keep the latest row from this selection #"Sorted Rows" = Table.Buffer(Table.Sort(#"Filtered Rows6",{{"Date", Order.Descending}})), #"Kept First Rows" = Table.FirstN(#"Sorted Rows",1) in #"Kept First Rows", // Example data - replace with your table Source1 = MyTable, // Here the function is called #"Added Custom" = Table.AddColumn(Source1, "BValue", each SelectB([D], [E], [F], [G], [Date])), #"Expanded BValue" = Table.ExpandTableColumn(#"Added Custom", "BValue", {"Value"}, {"ValueB"}), #"Added Custom1" = Table.AddColumn(#"Expanded BValue", "Difference", each if [Type]="A" then [Value]-[ValueB] else "") in #"Added Custom1"I understand as it does a lot of filtering for each of the rows, it's expected to be slow, but I think I'll have to think of something else if this has to run a few hours every day (I have daily refresh on my dataset)
Sorry, always forget to place a Buffer in order to prevent multiple calls.
Try to change your Source1-step to:
Source1 = Table.Buffer(MyTable),
