How to Join 2 excel sheets with a common column

[narendrasr]

Hi,
I have data in 2 sheets with a common column which needs to be combined to form a larger table with complete information. Is it possible to do in Excel.

The data in Table 1 is as below.

User Role
A AA
A BB
B AA
C BB
D ZZ

In the 2nd table, the data is as below:
Role Privilege
AA 123
AA 345
BB 456
BB 678
CC 910

The combined data needs to have a join of these tables and show output as below:
User Role Privilege
A AA 123
A AA 345
A BB 456
A BB 678
B AA 123
B AA 345
C BB 456
C BB 678

The able sample data is attached for easy reference.
Sample Data.xlsx

[AliGW]

Welcome to the forum.

Load the first table into PowerQuery and save as connection only, then load the second table and merge it with the first.

M Code:

Please Login or Register  to view this content.

You would need to combine two workbooks if the source tables are not in the same file.

If you need further assistance (if you have not used PQ before), just shout.

[narendrasr]

Hi Ali,
Thanks a lot for the quick reply. The output and the working sheet shared by you is exactly what I was looking for!
I am not familiar with Power Query but I'll use the sheet shared by you to get familiarized. I may come back to you if I have any further doubts on the same. Once again, thanks for the detailed and quick reply to my query. This forum is amazing!

Regards,
Narendra Rana

[Czeslaw]

Power Query

Please Login or Register  to view this content.