Need help merging two tables to bring over a column

[dufoq3]

Hi @Jason290, it is easy in power query using Merge Queries.

Merge Queries:

Expand System:

But be careful! At the beginning you have to remove duplicates from Table2:

If you don't delete duplicates in Table2, the result will be confusing:

Or you can add this as custom Column in Table1:

Text.Combine(Table.SelectRows(Table2, (x)=> x[ip address] = [ip address])[System], " | ")

Result:

[Jason290]

I don't want to concatenate the system names, I want to show them in seperate rows, and I don't want to remove duplicates from table2 because two different system can have the same ip address. but as you can see after doing either left outter or inner merge you get duplicate ip addresses for the ones where there are matching ip addresses between two different systems in table2. But I want the matching ip's in table2 to show once for each systems after the merge like shown in the desired result table. 

[dufoq3]

OK, add this as custom column to Table1

Table.SelectRows(Table2, (x)=> x[ip address] = [ip address])

 and then expand System

[Jason290]

That gives same result as a Left Outer join. which is not the desired result.

[dufoq3]

I don't understand what you need.

Expanded:

[Jason290]

it's listed somewhere down line 25-26. do a filter to see firstMac, you will see 192.168.1.6 and 192.168.1.12 listed twice. Same for secondMac

[dufoq3]

Check attached pbix file.

I've added System Number to Table2 and sort via this column which is mandantory.

With this it is possible to achive expected result, but maybe you will need to sort also Table1. 

Let me know if this is what you want.