cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chuckm4614 Frequent Visitor
Frequent Visitor

Compare two sets of data

Hello All - I'm new to PowerBI and I apologize in advance if this in the forum already and I missed it!

 

I have 3 different data sets (Table_AVI, Table_IND, Table_Zip) and each have a FullName column.  I need to compare the FullName columns in Table_IND and Table_Zip to the Table_AVI and tell me which Names are in both or possibly in all three. 

 

If there is a match, would it be possible to add a column to Table_AVI to show if the match was from Table_IND & or Table_Zip?

 

If this is possible, I wouild also like to perform the same using the Phone and also an email.

 

Thanks in advance for the help!

 

Chuck

4 REPLIES 4
jthomson New Contributor
New Contributor

Re: Compare two sets of data

Got to be some tricks you can do in Power Query for this one. You could:

- hack down IND and Zip to a single column with just the names

- add a custom column to IND that just says IND in every row and one in Zip that just says Zip

- Merge IND and Zip together on the name, add a column with conditional logic saying if the IND column's blank then Zip, else if the Zip column's blank then IND, else Both

- Merge that into AVI

 

Probably more efficient ways to do this but that should work

rajendran Super Contributor
Super Contributor

Re: Compare two sets of data

Assuiming you have defined the relationship properly, you can try the below calculated column.

IF( Table_AVI[Name] = EARLIER(RELATED( Table_IND[Name]) , "Match in IND", "No Match")

 

IF( Table_AVI[Name] = EARLIER(RELATED( Table_ZIP[Name]) , "Match in ZIP","No Match")

 

Thanks

Raj

 

 

 

 

chuckm4614 Frequent Visitor
Frequent Visitor

Re: Compare two sets of data

I have not defined a relationship at all.  I've attempted to Create one but get the error "you cant create a relationship between these two columns beacuse one of the columns must have unique values."  

Highlighted
rajendran Super Contributor
Super Contributor

Re: Compare two sets of data

ok, first you need to define relatonship between tables. It seems that you have many to many relationship.

 

Power BI doesnt support this relationship, so you need to create a bridge table to create unique values which can be used for join.You can refer the below link :

 

https://community.powerbi.com/t5/Report-Server/Many-to-Many-Relationship-between-tables-and-show-dat...

 

Thanks

Raj

 

 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 217 members 2,333 guests
Please welcome our newest community members: