Comparing data in two tables to show differences

Question

Hi all,&nbsp;I have a requirement where I need to compare two tables of data and would like to highlight differences. The majority of the data in both tables will be the same; however, there can be deleted rows or additional rows in either table and I would like to create a query that would highlight this. In addition, if the row exists in both tables but there is a difference in a column value, I would need to highlight this too.&nbsp;For example, in the data set below there are three differences between the two tables.&nbsp;&nbsp;1. Addition of 2 chocolate product rows2. Week 2 of the gum products has been removed3. Week 2 of the mint product is 40Table 1ID&nbsp; &nbsp; Product&nbsp; &nbsp; &nbsp;Week&nbsp; &nbsp; &nbsp;Cost1&nbsp; &nbsp; &nbsp; Gum&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1001&nbsp; &nbsp; &nbsp; Gum&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1002&nbsp; &nbsp; &nbsp; Mint&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;502&nbsp; &nbsp; &nbsp; Mint&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;50&nbsp;Table 2ID&nbsp; &nbsp; Product&nbsp; &nbsp; &nbsp;Week&nbsp; &nbsp; &nbsp;Cost1&nbsp; &nbsp; &nbsp; Gum&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1002&nbsp; &nbsp; &nbsp; Mint&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;502&nbsp; &nbsp; &nbsp; Mint&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;403&nbsp; &nbsp; &nbsp;Choc&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1203&nbsp; &nbsp; &nbsp;Choc&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;120&nbsp;I am looking to find a way in PowerBI where I can produce an output that shows me the changes in a similar way to below.&nbsp;I have been struggling with finding a way to do this and was wondering if anyone had any suggestions or if this was even possible?&nbsp;Expected outputID&nbsp; &nbsp; Product&nbsp; &nbsp;Week&nbsp; &nbsp; Cost1&nbsp; &nbsp; &nbsp; Gum&nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1002&nbsp; &nbsp; &nbsp; Mint&nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 403&nbsp; &nbsp; &nbsp; Choc&nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1203&nbsp; &nbsp; &nbsp; Choc&nbsp; &nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 120&nbsp;Any help would be much appreciated!&nbsp;&nbsp;

anonymous · Answer

Anonymous&nbsp;wrote:Hi all,&nbsp;I have a requirement where I need to compare two tables of data and would like to highlight differences. The majority of the data in both tables will be the same; however, there can be deleted rows or additional rows in either table and I would like to create a query that would highlight this. In addition, if the row exists in both tables but there is a difference in a column value, I would need to highlight this too.&nbsp;For example, in the data set below there are three differences between the two tables.&nbsp;&nbsp;1. Addition of 2 chocolate product rows2. Week 2 of the gum products has been removed3. Week 2 of the mint product is 40Table 1ID&nbsp; &nbsp; Product&nbsp; &nbsp; &nbsp;Week&nbsp; &nbsp; &nbsp;Cost1&nbsp; &nbsp; &nbsp; Gum&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1001&nbsp; &nbsp; &nbsp; Gum&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1002&nbsp; &nbsp; &nbsp; Mint&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;502&nbsp; &nbsp; &nbsp; Mint&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;50&nbsp;Table 2ID&nbsp; &nbsp; Product&nbsp; &nbsp; &nbsp;Week&nbsp; &nbsp; &nbsp;Cost1&nbsp; &nbsp; &nbsp; Gum&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1002&nbsp; &nbsp; &nbsp; Mint&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;502&nbsp; &nbsp; &nbsp; Mint&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;403&nbsp; &nbsp; &nbsp;Choc&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1203&nbsp; &nbsp; &nbsp;Choc&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;120&nbsp;I am looking to find a way in PowerBI where I can produce an output that shows me the changes in a similar way to below.&nbsp;I have been struggling with finding a way to do this and was wondering if anyone had any suggestions or if this was even possible?&nbsp;Expected outputID&nbsp; &nbsp; Product&nbsp; &nbsp;Week&nbsp; &nbsp; Cost1&nbsp; &nbsp; &nbsp; Gum&nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1002&nbsp; &nbsp; &nbsp; Mint&nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 403&nbsp; &nbsp; &nbsp; Choc&nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1203&nbsp; &nbsp; &nbsp; Choc&nbsp; &nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 120&nbsp;Any help would be much appreciated!&nbsp;&nbsp;Hi,Please check this solutionhttps://community.powerbi.com/t5/Desktop/Finding-differences-between-tables/m-p/328736#M146789Regards

Forum Discussion

Comparing data in two tables to show differences

1 Reply

Related content

Compare different tables

Comparing values between different tables

Table Sort , sort order , comparer

Compare two columns in different tables

Comparing two tables to find differences