cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SteveG_91
Helper I
Helper I

Compare Two Tables

I have two tables containing application data, Table1 and Table2.  They are related to each other using the common column, APP_NUM.  Table1 has only one row per APP_NUM while Table2 can have 1 or more rows per APP_NUM.  Each table contains a phone number.  In Table1 the phone number belongs to the originator of the application.  In Table2 the phone numbers belong to each applicant.  What I would like to do is compare the two tables using the APP_NUM and indicate on Table2 if the phone number of any of the applicants is the same phone number as that of the originator on Table1.

 

Here is the table structure of each table

Table1 
APP_NUMPHONE_NUM
123458885551111
777773333333333

 

Table2  
APP_NUMROLEPHONE
12345APPLICANT 11111111111
12345APPLICANT 22222222222
77777APPLICANT 18888888888
77777APPLICANT 23333333333

 

I would like to add a column to Table2 that indicates if there is a match between PHONE and PHONE_NUM using APP_NUM to join the two tables.  So it would look like this

Table2   
APP_NUMROLEPHONEMATCH
12345APPLICANT 11111111111No
12345APPLICANT 22222222222No
77777APPLICANT 18888888888No
77777APPLICANT 23333333333Yes

 

Any suggestion on how to accomplish this, either in Power Query or a DAX measure?

 

Thanks in advance!

2 ACCEPTED SOLUTIONS
v-xinruzhu-msft
Community Support
Community Support

Hi @SteveG_91  ,

Try this:

MATCH = CONTAINSSTRING('Table (2)'[PHONE],RELATED('Table'[PHONE_NUM]))

Output:

vxinruzhumsft_0-1670313236859.png

 

Best Regards,

Yolo Zhu

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

Hi @SteveG_91 

 

You can try this:

MATCH = IF(ISBLANK(RELATED('Table'[APP_NUM])),FALSE(),CONTAINSSTRING('Table (2)'[PHONE],RELATED('Table'[PHONE_NUM])))
vxinruzhumsft_0-1670396318314.png

Best Regards!

 

Yolo

View solution in original post

8 REPLIES 8
SteveG_91
Helper I
Helper I

Compare Two Tables
12m ago

I have two tables containing application data, Table1 and Table2.  They are related to each other using the common column, APP_NUM.  Table1 has only one row per APP_NUM while Table2 can have 1 or more rows per APP_NUM.  Each table contains a phone number.  In Table1 the phone number belongs to the originator of the application.  In Table2 the phone numbers belong to each applicant.  What I would like to do is compare the two tables using the APP_NUM and indicate on Table2 if the phone number of any of the applicants is the same phone number as that of the originator on Table1.

 

Here is the table structure of each table

Table1 
APP_NUMPHONE_NUM
123458885551111
777773333333333

 

Table2  
APP_NUMROLEPHONE
12345APPLICANT 11111111111
12345APPLICANT 22222222222
77777APPLICANT 18888888888
77777APPLICANT 23333333333

 

I would like to add a column to Table2 that indicates if there is a match between PHONE and PHONE_NUM using APP_NUM to join the two tables.  So it would look like this

Table2   
APP_NUMROLEPHONEMATCH
12345APPLICANT 11111111111No
12345APPLICANT 22222222222No
77777APPLICANT 18888888888No
77777APPLICANT 23333333333Yes

 

Any suggestion on how to accomplish this, either in Power Query or a DAX measure?

 

Thanks in advance!

Hi @SteveG_91 , create this relationship:

Bifinity_75_0-1670271239330.png

 

And create this calculate column:

Match = if(RELATED(Table1[APP_NUM])=BLANK(),"No","Yes")

The result:

Bifinity_75_1-1670271304416.png

 

Best Regards

 

 

 

 

So I already have a one to many relationship between these two tables on the APP_NUM and the PHONE and PHONE_NUM would be a many to many since the phone number can appear multiple times on each table.  When I look to see if the phone number is on both tables it has to be specific to the same APP_NUM.  In other words, for APP_NUM 12345 on Table1 I only want to compare the phone numbers to APP_NUM 12345 on Table2.  Then compare the phone numbers for APP_NUM 77777, etc.

v-xinruzhu-msft
Community Support
Community Support

Hi @SteveG_91  ,

Try this:

MATCH = CONTAINSSTRING('Table (2)'[PHONE],RELATED('Table'[PHONE_NUM]))

Output:

vxinruzhumsft_0-1670313236859.png

 

Best Regards,

Yolo Zhu

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

 

One follow-up question.  In some instances the phone number is missing (null) from Table1 but is on Table2.  The measure is returning True for those.  How can I make it return False?

Hi @SteveG_91 

 

You can try this:

MATCH = IF(ISBLANK(RELATED('Table'[APP_NUM])),FALSE(),CONTAINSSTRING('Table (2)'[PHONE],RELATED('Table'[PHONE_NUM])))
vxinruzhumsft_0-1670396318314.png

Best Regards!

 

Yolo

Thanks again, this worked perfectly!

This does just what I needed.  Thank you!!!

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.