Forum Discussion
Identifying only records that changed
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!
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.
3 Replies
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.
Thank you so much for the time and effort you dedicated to assisting me with this solution. This is exactly what I needed. Thanks! 👍
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.
