Forum Discussion
Calculate difference from latest value
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"
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),