07-25-2018 08:31 AM
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!
07-25-2018 08:40 AM
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
07-25-2018 08:49 AM
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")
07-25-2018 09:17 AM
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."
07-25-2018 09:39 AM
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 :