If previous line value matches using Power Query

[Anonymous]

Hi,

I'm new to using Power Query. I think I can solve the issue in DAX but I'm concerned about performance issues and think it would be better to solve using Power Query M.

I've got a log of changes to a table. The log is simply the entire tables record logged as a csv when ever the record is modified. I just want a list of when status update changes.

I figure I can just sort by 'Modified By' and by 'Modified Date' then remove duplicates on the update and by. But the issue is if someone sets the comment back to a previously used comment. Then that is also removed.

Solution is, if previous row matches Status and Modified by, then flag as TRUE. Remove all lines with TRUE.

In DAX

My attempt at Power Query M so far which is producing error, I know that query not working but feel like failing at the first hurdle.

I've tried replicating this, but just can't get it to work for the Power Query and I cant understand how its functioning.

Solved: Referencing a previous Row - Microsoft Power BI Community

I've had a go myself. This based on the documentation feels like it should return a singe record of the previous index, but its not.

Record.SelectFields - PowerQuery M | Microsoft Docs

Anyone got any ideas?

[AlexisOlson · Accepted solution]

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