Forum Discussion
If previous line value matches using Power Query
- 4 years ago
I've added a solution to the thread you mentioned that involves double indexing and a self-merge to retrieve the prior row.
Here's an example closer to your data:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUaoEYkN9Q30jAyMjpVidaCUnoEAFWNAIIegMFzTGJmiCTbspNkEzhCDCdnOEoAtc0AIqGAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Task Owner Update" = _t, #"Modified By" = _t, #"Modified Date" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Task Owner Update", type text}, {"Modified By", type text}, {"Modified Date", type date}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index0", 0, 1, Int64.Type), #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index1", 1, 1, Int64.Type), #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index0"}, #"Added Index1", {"Index1"}, "PrevRow", JoinKind.LeftOuter), #"Expanded PrevRow" = Table.ExpandTableColumn(#"Merged Queries", "PrevRow", {"Task Owner Update", "Modified By"}, {"PrevRow.Task Owner Update", "PrevRow.Modified By"}), #"Added Custom" = Table.AddColumn(#"Expanded PrevRow", "Filter", each [Task Owner Update] = [PrevRow.Task Owner Update] and [Modified By] = [PrevRow.Modified By], type logical), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Filter] <> true)), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Task Owner Update", "Modified By", "Modified Date"}) in #"Removed Other Columns"Here's a shorter version that merges on three columns rather than doing the equality check as an extra step and uses an anti-join instead of a filter step:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUaoEYkN9Q30jAyMjpVidaCUnoEAFWNAIIegMFzTGJmiCTbspNkEzhCDCdnOEoAtc0AIqGAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Task Owner Update" = _t, #"Modified By" = _t, #"Modified Date" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Task Owner Update", type text}, {"Modified By", type text}, {"Modified Date", type date}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index0", 0, 1, Int64.Type), #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index1", 1, 1, Int64.Type), #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index0", "Task Owner Update", "Modified By"}, #"Added Index1", {"Index1", "Task Owner Update", "Modified By"}, "Filter", JoinKind.LeftAnti), #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Index0", "Index1", "Filter"}) in #"Removed Columns"Here are some additional threads related to referencing prior rows:
https://community.powerbi.com/t5/Desktop/Find-difference-between-2-rows/m-p/2159155
https://community.powerbi.com/t5/Desktop/Calculating-Daily-values-from-Cumulative-Total/m-p/2198969
I've added a solution to the thread you mentioned that involves double indexing and a self-merge to retrieve the prior row.
Here's an example closer to your data:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUaoEYkN9Q30jAyMjpVidaCUnoEAFWNAIIegMFzTGJmiCTbspNkEzhCDCdnOEoAtc0AIqGAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Task Owner Update" = _t, #"Modified By" = _t, #"Modified Date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Task Owner Update", type text}, {"Modified By", type text}, {"Modified Date", type date}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index0", 0, 1, Int64.Type),
#"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index1", 1, 1, Int64.Type),
#"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index0"}, #"Added Index1", {"Index1"}, "PrevRow", JoinKind.LeftOuter),
#"Expanded PrevRow" = Table.ExpandTableColumn(#"Merged Queries", "PrevRow", {"Task Owner Update", "Modified By"}, {"PrevRow.Task Owner Update", "PrevRow.Modified By"}),
#"Added Custom" = Table.AddColumn(#"Expanded PrevRow", "Filter", each [Task Owner Update] = [PrevRow.Task Owner Update] and [Modified By] = [PrevRow.Modified By], type logical),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Filter] <> true)),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Task Owner Update", "Modified By", "Modified Date"})
in
#"Removed Other Columns"
Here's a shorter version that merges on three columns rather than doing the equality check as an extra step and uses an anti-join instead of a filter step:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUaoEYkN9Q30jAyMjpVidaCUnoEAFWNAIIegMFzTGJmiCTbspNkEzhCDCdnOEoAtc0AIqGAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Task Owner Update" = _t, #"Modified By" = _t, #"Modified Date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Task Owner Update", type text}, {"Modified By", type text}, {"Modified Date", type date}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index0", 0, 1, Int64.Type),
#"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index1", 1, 1, Int64.Type),
#"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index0", "Task Owner Update", "Modified By"}, #"Added Index1", {"Index1", "Task Owner Update", "Modified By"}, "Filter", JoinKind.LeftAnti),
#"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Index0", "Index1", "Filter"})
in
#"Removed Columns"
Here are some additional threads related to referencing prior rows:
https://community.powerbi.com/t5/Desktop/Find-difference-between-2-rows/m-p/2159155
https://community.powerbi.com/t5/Desktop/Calculating-Daily-values-from-Cumulative-Total/m-p/2198969