Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

If previous line value matches using Power Query

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 ...
  • AlexisOlson's avatar
    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