Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Combining data sets with duplicate information (real tester!!)

Hi all!   I am trying to combine two data sets which contain duplicate information. Instead of getting my desired outcome of assigning a unique identifier, I am left with more duplicates.    Data...
Need Help
HotChilli's avatar
HotChilli
Icon for Community Champion rankCommunity Champion
5 years ago

What is the desired outcome with the example data?  Show us 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thanks for getting back to me HotChilli!! I would like to get the following data:

    NamePosition ID
    a1
    b2
    c3
    d4

     

    Each row in 'Name' should be linked to an appropriate, unique 'Position ID' via the type of 'Role'. 

     

    Does that make sense?

     

    Let me know if you have any questions, or if you require a greater data set!

     

    Thanks again!!!

    • mahoneypat's avatar
      mahoneypat
      Icon for Microsoft Employee rankMicrosoft Employee
      5 years ago

      You can add a sub index for role column in both tables and then do a merge using both columns.  However, if you truly have the same number of rows in both tables, you can just use Table.FromColumns with the formula shown in the pic below to get the shown table.  DS1 and DS2 and the names I gave to the two queries/tables.

       

       

      Regards,

      Pat

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi mahoneypat!!

         

        Thanks for getting back to me 🙂 

         

        A sub-index is a great idea! However, my DS is quite large and it would be time consuming to add in a sub-index to specific each specific role and ensure they matched up correctly.

         

        I was hoping to be able to implement logic which could exclude previoulsy used Position IDs.

         

        Thanks again Pat you are a legend