Forum Discussion
Compare between 2 tables and highlight field changes
I have 2 tables and want to compare the values and show the row of the changed field and highlight the changed data in a summary table, as below;
Table 1
P&ID Rev.
P&ID Piperack/Interconnect Line Number
| E104042-01-101 Sheet 1 | Interconnect Flare | 0 | HL-3/4"-V7400-8.1-L11 |
| E104042-01-101 Sheet 1 | Interconnect Flare | 0 | HL-1"-V7400-4.1-L11 |
Table 2
P&ID Rev.
P&ID Piperack/Interconnect Line Number
| E104042-01-101 Sheet 1 | Interconnect Flare | 0 | HL-3/4"-V7400-8.1-L11 |
| E104042-01-101 Sheet 1 | Interconnect Flare | 0 | HL-2"-V7400-4.1-L11 |
Summary Table (Highlighting Differences)
P&ID Rev.
P&ID Piperack/Interconnect Line Number
| E104042-01-101 Sheet 1 | Interconnect Flare | 0 | HL-2"-V7400-4.1-L11 |
Any help would be greatly appreciated
4 Replies
- DataInsights
Super User
Try this calculated table. You can reverse the arguments in EXCEPT to identify what is in Table1 but not in Table2.
Difference Table = EXCEPT ( Table2, Table1 )This is great, as long as the tables have the same columns, it creates a whole role with the changed data field. 👍 Thank you
How would I identify the changing field? Is it possible to embed an argument into the above to identify which field has the changed data? Or would it be somehow conditional formatted within the display of the table in the Report?
- DataInsights
You could create a flag that would identify any difference in the specific field (as compared to Table1), and then apply conditional formatting in a visual based on the flag. Would you be able to provide a screenshot of the expected result?
PaulDBrownCommunity Champion
If you wish to use a measure (and identify rows missing from either table if so you wish), you can create dimension tables for the fields and set the model like this:
Then create a measure as follows:
Excluded from = VAR T1 = COUNTROWS(EXCEPT(Table1, Table2)) VAR T2 = COUNTROWS(EXCEPT(Table2, Table1)) RETURN SWITCH(TRUE(), T1 = 1, "Not in Table 2", T2 = 1, "Not in Table 1")Then create a table visual with the fields from the dimension tables and add the measure:
