Hi ALL, Hope every one is doing well. Need your help in comparing 2 columns from different tables.Putting down my sample data.Table 1 :IDDepedency1234Client1234Testing1234Engg8125Support6542Testing6542Support6542Reporting6542Client9654Support5420 Table 2:IDDepedency1234Client1234Engg6542Engg8125Client8125Engg8125Support9654Client5420TestingI have to showup all the ID's where there is a Mismatch in Dependencies ( which are present in Table 2 but not there in Table 1) As per the sample data above the ouptput has to be IDT1.DependencyT2.Dependency8125SupportClient Engg Support6542TestingEnggSupport Reporting Client 9654SupportClient5420 TestingPlease help.

Abbi here you go https://www.dropbox.com/t/x3YgD9Jf7DsM58i6Code for Table 1 remains the same. Code for table 2 is updated as followsT2 Dependancy = VAR MissingItems = EXCEPT ( Table2, Table1 ) VAR Result = IF ( HASONEVALUE ( Append1[ID] ), CONCATENATEX ( MissingItems, Table2[Depedency], "," & UNICHAR(10) ) ) RETURN Result If I solve your problem please consider marking my reply as Accepted. Liks (Kudos) are also appreciated.Thank you

Forum Discussion

Abbi

Helper I

4 years ago

Solved

How to Compare two columns from 2 different tables

Hi ALL,

Hope every one is doing well. Need your help in comparing 2 columns from different tables.

Putting down my sample data.

Table 1 :

ID	Depedency
1234	Client
1234	Testing
1234	Engg
8125	Support
6542	Testing
6542	Support
6542	Reporting
6542	Client
9654	Support
5420

Table 2:

ID	Depedency
1234	Client
1234	Engg
6542	Engg
8125	Client
8125	Engg
8125	Support
9654	Client
5420	Testing

I have to showup all the ID's where there is a Mismatch in Dependencies ( which are present in Table 2 but not there in Table 1)

As per the sample data above the ouptput has to be

ID	T1.Dependency	T2.Dependency
8125	Support	Client
	Engg
	Support
6542	Testing	Engg
Support
Reporting
Client
9654	Support	Client
5420		Testing

Please help.

tamerj1
4 years ago
Abbi here you go https://www.dropbox.com/t/x3YgD9Jf7DsM58i6

Code for Table 1 remains the same. Code for table 2 is updated as follows
T2 Dependancy = VAR MissingItems = EXCEPT ( Table2, Table1 ) VAR Result = IF ( HASONEVALUE ( Append1[ID] ), CONCATENATEX ( MissingItems, Table2[Depedency], "," & UNICHAR(10) ) ) RETURN Result

If I solve your problem please consider marking my reply as Accepted. Liks (Kudos) are also appreciated.
Thank you

8 Replies

tamerj1
Community Champion
4 years ago
Hi Abbi ,
Here is the sample file https://www.dropbox.com/t/TRMjGhgQmkfMLS9m
Many ways to get to that but I prefer the following:
In order to get the complete unique list of all ID's, impot your tables into power query and follow these steps
Then "Close&Load". Open the Relationship tab and make sure relatiohships are set as follows
In the visuals tab slect the Matrix visual and drag the ID column from the appended table into the rows.
Use the following code to write your measures (one measure for each table):
T(n) Dependancy = IF ( HASONEVALUE ( Append1[ID] ), CONCATENATEX ( Table(n), Table(n)[Depedency], "," & UNICHAR(10) ) )
Your report will look something like this:
Abbi
Helper I
4 years ago
Hi tamerj1 I have same vales in Dependency but T1 Dependency is taking Client,Engg where as T2 Dependency is taking the vale as Engg,Client.

Please help.
If any other approach please let me know
- tamerj1
  Community Champion
  4 years ago
  Abbi Please clarify further.
  Do you mean that under T1 Dependancy you need to see the missing items that are there in Table2 and vice versa?
  - tamerj1
    Community Champion
    4 years ago
    Abbi Please double check your traget result table. It is completely confusing
    ID T1.Dependency T2.Dependency
    8125 Support Client
    Engg
    Support
    6542 Testing Engg
    Support
    Reporting
    Client
    9654 Support Client
    5420 Testing
Abbi
Helper I
4 years ago
Greg_Deckler , Please help