Identifying only records that changed

[mmunozjr5]

I have the following table in Power BI, this table gets its data from Excel files stored in a folder that contains daily extracts from a source system. Sometimes users change records in the source system and we need to be able to identify which records were changed and which value or values in those records were changed, from what value to what value. How can we do that in power bi

Here is the sample table

Order number	Order Date	Category	Description	Buyer	Source.Name
AXC67	########	Toys	Plastic balls	June Rechnick	Order_20240229.xlsx
M4598	########	Exercise	Eleastic Band	John Smith	Order_20240229.xlsx
M4598	########	Toys	Eleastic Band	John Smith	Order_20240331.xlsx
RFA78	########	Office	Mouse Pads	Sam Poliye	Order_20240331.xlsx
SDE45	########	Exercise	Power Press	Sam Kilonm	Order_20240331.xlsx
XXA76	########	Dental	Retainers	Serena Qerty	Order_20240430.xlsx

Here is the expected output that I am looking for.

Thank you, for your assistance!

[Anonymous · Accepted solution]

Hi, @mmunozjr5 

You can try the following methods. Make a judgment about whether there is a change for each column.

Category change = 
Var _count1=CALCULATE(COUNT('Table'[Order number]),ALLEXCEPT('Table','Table'[Order number]))
Var _count2=CALCULATE(COUNT('Table'[Category]),ALLEXCEPT('Table','Table'[Category],'Table'[Order number]))
RETURN
IF(_count1=_count2,BLANK(),"Category")

Date = DATE(RIGHT(LEFT([Source.Name],10),4),RIGHT(LEFT([Source.Name],12),2),RIGHT(LEFT([Source.Name],14),2))

New table:

New table = FILTER(SUMMARIZE('Table','Table'[Order number],'Table'[Category change]),[Category change]<>BLANK())

Column:

Change date = CALCULATE(MAX('Table'[Date]),FILTER('Table',[Order number]=EARLIER('New table'[Order number])))

NewValue = CALCULATE(MAX('Table'[Category]),FILTER('Table',[Order number]=EARLIER('New table'[Order number])&&[Date]=EARLIER('New table'[Change date])))

OldValue = CALCULATE(MAX('Table'[Category]),FILTER('Table',[Order number]=EARLIER('New table'[Order number])&&[Date]=MIN('Table'[Date])))

Is this the result you expected?

Best Regards,

Community Support Team _Charlotte

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

[mmunozjr5]

Thank you so much for the time and effort you dedicated to assisting me with this solution. This is exactly what I needed. Thanks! 👍

[collinq]

HI @mmunozjr5 ,

The easiest and best way would be for the data itself to have a "changed date" or a "changed" indicator.

If you do not have that but you do have a daily output the next best thing would be to create a report that takes each day's record and then compares them against the next day's record.  For that to properly work though you would have to have the exact same records each day but if these are orders it wouldn't be the same every day I suspect.

So, if you do get a daily output and those output files remain static then the method would be to get each day's output and then first determine all the "duplicate rows" based on the key field(s).  That will let you know which ones are the same in both documents and are thefore eligible to be in your "changed" report.

The next step would be to decide if you want to compare in Query Editor in DAX.  If you do it in Query Editor, then you would have to merge the queries in question and then compare fields of the same name against each other and see if they are different (custom column with something like if "ColumnATable1 equals ColumnATable2 then 'all good' otherwise 'different') and then you would have to determine the difference.

In DAX, the tables would have to have a relationship and then you could do something like "Different = Table1[ColumnA] <> Table2[ColumnA] then "different" else "all good" .  And then you could have the output that is different.

If you do not know the top fields you want and/or the fields they can change, then you would have to do this for every single field.