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
chuckm4614
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
Anonymous
Not applicable

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

 

 

 

 

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

Anonymous
Not applicable

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

 

 

jthomson
Solution Sage
Solution Sage

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

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.