Reply
Frequent Visitor
Posts: 6
Registered: ‎07-25-2018

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

Super User
Posts: 442
Registered: ‎07-12-2017

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

Super User
Posts: 668
Registered: ‎11-01-2017

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

 

 

 

 

Frequent Visitor
Posts: 6
Registered: ‎07-25-2018

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
Super User
Posts: 668
Registered: ‎11-01-2017

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