cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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_NUM PHONE_NUM 12345 8885551111 77777 3333333333

 Table2 APP_NUM ROLE PHONE 12345 APPLICANT 1 1111111111 12345 APPLICANT 2 2222222222 77777 APPLICANT 1 8888888888 77777 APPLICANT 2 3333333333

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_NUM ROLE PHONE MATCH 12345 APPLICANT 1 1111111111 No 12345 APPLICANT 2 2222222222 No 77777 APPLICANT 1 8888888888 No 77777 APPLICANT 2 3333333333 Yes

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

2 ACCEPTED SOLUTIONS
Community Support

Hi @SteveG_91  ,

Try this:

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

Output:

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.

Community Support

You can try this:

MATCH = IF(ISBLANK(RELATED('Table'[APP_NUM])),FALSE(),CONTAINSSTRING('Table (2)'[PHONE],RELATED('Table'[PHONE_NUM])))

Best Regards!

Yolo

8 REPLIES 8
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_NUM PHONE_NUM 12345 8885551111 77777 3333333333

 Table2 APP_NUM ROLE PHONE 12345 APPLICANT 1 1111111111 12345 APPLICANT 2 2222222222 77777 APPLICANT 1 8888888888 77777 APPLICANT 2 3333333333

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_NUM ROLE PHONE MATCH 12345 APPLICANT 1 1111111111 No 12345 APPLICANT 2 2222222222 No 77777 APPLICANT 1 8888888888 No 77777 APPLICANT 2 3333333333 Yes

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

Solution Sage

Hi @SteveG_91 , create this relationship:

And create this calculate column:

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

The result:

Best Regards

Helper I

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.

Community Support

Hi @SteveG_91  ,

Try this:

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

Output:

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.

Helper I

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?

Community Support

You can try this:

MATCH = IF(ISBLANK(RELATED('Table'[APP_NUM])),FALSE(),CONTAINSSTRING('Table (2)'[PHONE],RELATED('Table'[PHONE_NUM])))

Best Regards!

Yolo

Helper I

Thanks again, this worked perfectly!

Helper I

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

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors