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

See when key Fabric features will launch and what’s already live, all in one place and always up to date. Explore the new Fabric roadmap

Reply
PBI_Q
New Member

Comparing two Excel tables

Hi All,

 

I was looking for a way to have powerbi to compare two spreadsheets, hihlight the changed filed and be able to output only the changed fields from the cells data to a table visual.

 

Thanks

 

1 ACCEPTED SOLUTION
v-yanjiang-msft
Community Support
Community Support

Hi @PBI_Q ,

If you want to show the changed fields in a visual, here's my solution.

Sample:

Table1

vkalyjmsft_0-1665733263358.png

Table2

vkalyjmsft_1-1665733282929.png

The two tables are related with the compared column.

vkalyjmsft_2-1665733424946.png

Create a measure.

Check =
IF ( MAX ( 'Table2'[Column] ) <> MAX ( 'Table1'[Column] ), 1, 0 )

Put table2 in a visual, and put the measure in the visual filter, then set its value to 1.

vkalyjmsft_3-1665733601556.png

After click Apply filter, get the correct result.

vkalyjmsft_4-1665733673063.png

I attach my sample below for your reference.

 

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

View solution in original post

2 REPLIES 2
v-yanjiang-msft
Community Support
Community Support

Hi @PBI_Q ,

If you want to show the changed fields in a visual, here's my solution.

Sample:

Table1

vkalyjmsft_0-1665733263358.png

Table2

vkalyjmsft_1-1665733282929.png

The two tables are related with the compared column.

vkalyjmsft_2-1665733424946.png

Create a measure.

Check =
IF ( MAX ( 'Table2'[Column] ) <> MAX ( 'Table1'[Column] ), 1, 0 )

Put table2 in a visual, and put the measure in the visual filter, then set its value to 1.

vkalyjmsft_3-1665733601556.png

After click Apply filter, get the correct result.

vkalyjmsft_4-1665733673063.png

I attach my sample below for your reference.

 

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

ImkeF
Community Champion
Community Champion

Hi @PBI_Q ,
yes, that's called a full anti join: (4) Performing a full Anti Join in Power Query - Compare Two Tables - YouTube

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
May PBI 25 Carousel

Power BI Monthly Update - May 2025

Check out the May 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

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

Top Solution Authors
Top Kudoed Authors