Forum Discussion

7 Replies

  • dufoq3's avatar
    dufoq3
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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's avatar
      Jason290
      Frequent Visitor
      2 years ago

      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's avatar
        dufoq3
        Icon for Community Champion rankCommunity Champion
        2 years ago

        OK, add this as custom column to Table1

         

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

         

         

         and then expand System