Forum Discussion
Difference between two sets of data
I have created identical pie charts from two different tables that should include the same data, in order to see if there are any discrepancies.
I'd like to find a way of showing which data is not shown within both tables, without having to export the data and then remove any duplicates etc.
Provided the two tables have a unique ID colunm then you can use the Query editor to merge on a lefter anti join for both tables then combine these into 1 table
step 1 - open query editor
step 2 click on the merge query dropdown in the home table a select merge queries as new
Select your two tables and the id colunm in each then select Left Anti(rows only in first)
This will create a new table called Merge1 that has any values that appear in the first table but not in the second
Step 3 - repeat these steps changing the top table to table 2 and the bottom to table 1
this will create a merge2 table that has any values in table two that are not in table 1.
step 4 - merge these two merge tables into one final table
This Will create a final table merge3 that has all values that are missing in one of the tables
you can then use this new table to create a list of any values that are missing in each table. as shown in the third table above we can see that 5 is missing from table 1
if you are also wanting to see if values are diffrent then create your key olunm using the value aswell
for example above Create a concat colunm of ID and value (1-50) and use this in the merge if the ID 1 exsists in both but the value in one is 50 and in the other is 60 then you would get two distinct ids and this would mean that both would appear in the missing values table.
If this Post solved your isseus then please mark the reply as a solution
3 Replies
Provided the two tables have a unique ID colunm then you can use the Query editor to merge on a lefter anti join for both tables then combine these into 1 table
step 1 - open query editor
step 2 click on the merge query dropdown in the home table a select merge queries as new
Select your two tables and the id colunm in each then select Left Anti(rows only in first)
This will create a new table called Merge1 that has any values that appear in the first table but not in the second
Step 3 - repeat these steps changing the top table to table 2 and the bottom to table 1
this will create a merge2 table that has any values in table two that are not in table 1.
step 4 - merge these two merge tables into one final table
This Will create a final table merge3 that has all values that are missing in one of the tables
you can then use this new table to create a list of any values that are missing in each table. as shown in the third table above we can see that 5 is missing from table 1
if you are also wanting to see if values are diffrent then create your key olunm using the value aswell
for example above Create a concat colunm of ID and value (1-50) and use this in the merge if the ID 1 exsists in both but the value in one is 50 and in the other is 60 then you would get two distinct ids and this would mean that both would appear in the missing values table.
If this Post solved your isseus then please mark the reply as a solution
it didn't quite resolve the matter, however did indicate a way of which i could do it.
I created two visual tables which included columns from both background tables which should be identical. I then filtered one of these columns to "only show blanks" in one visual, and the same filter against the other visual for the other column.
this gave me two lists of discrepencies which i could then merge.
thank you,
