Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Comparing data in two tables to show differences

Hi all,

 

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.

 

For example, in the data set below there are three differences between the two tables. 

 

1. Addition of 2 chocolate product rows

2. Week 2 of the gum products has been removed

3. Week 2 of the mint product is 40


Table 1

ID    Product     Week     Cost
1      Gum          1            100

1      Gum          2            100
2      Mint          1             50
2      Mint          2             50

 

Table 2

ID    Product     Week     Cost
1      Gum          1            100
2      Mint          1             50
2      Mint          2             40
3     Choc          1             120
3     Choc          2             120

 

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. 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?

 

Expected output

ID    Product   Week    Cost
1      Gum        2           100
2      Mint        1            40
3      Choc       1            120
3      Choc       2            120

 

Any help would be much appreciated! 

 

1 REPLY 1
aswin_k
Frequent Visitor


@Anonymous wrote:

Hi all,

 

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.

 

For example, in the data set below there are three differences between the two tables. 

 

1. Addition of 2 chocolate product rows

2. Week 2 of the gum products has been removed

3. Week 2 of the mint product is 40


Table 1

ID    Product     Week     Cost
1      Gum          1            100

1      Gum          2            100
2      Mint          1             50
2      Mint          2             50

 

Table 2

ID    Product     Week     Cost
1      Gum          1            100
2      Mint          1             50
2      Mint          2             40
3     Choc          1             120
3     Choc          2             120

 

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. 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?

 

Expected output

ID    Product   Week    Cost
1      Gum        2           100
2      Mint        1            40
3      Choc       1            120
3      Choc       2            120

 

Any help would be much appreciated! 

 


Hi,
Please check this solution
https://community.powerbi.com/t5/Desktop/Finding-differences-between-tables/m-p/328736#M146789
Regards

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.