Detecting Data Changes with Type Text

[Laurendmurray]

I am looking to identify instances where a column's value differs from the previous record. Currently, we have observed scenarios where an individual's classification may vary from the prior month. As our data is updated monthly, overwriting existing information, our initial focus is on implementing an incremental refresh to capture and preserve incoming data. Subsequently, our goal is to establish a mechanism to pinpoint changes within this column. We aim to flag any occurrence where the classification differs from the previous record. Notably, conventional methods like detecting data changes may not be applicable here due to the column containing text data, limiting the use of functions like max, min, or sum. I would appreciate any insights or recommendations you may have on this matter.

[Anonymous]

Hi,

Thanks for the solution watkinnc and lbendlin offered, and i wanto offer some more information for user to refer to.

hello @Laurendmurray , based on your descriotion, you can consider to add an index column in power query, then in power destop, you can filter the last record by using offset() function with the index column. you can refer to the following link about the offset() function.

OFFSET function (DAX) - DAX | Microsoft Learn

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Not sure how your data is set up as in does the data accumulate every month. Nevertheless, I would first sort by person and then by date, then I would make an index on that table starting at one. Then I would Reference that table in a new query, Make another index column starting at zero, and then Left join them on Index and Index 1. Then you can just do the row comparison, like if this equals that then "Same" else "Changed".

--Nate

[lbendlin]

Incremental Refresh is the wrong tool for that, it expects the data to be immutable (Unless you choose to combine the item ID and the Midified Date into the primary key).

Power BI has no memory. You need to do your snapshots or Change Data Capture further upstream.