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
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
Microsoft 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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      peraphs to get a good specific solution you should provide more info about your data sets.

       

      Some, but not all possible question about your datasets
      How big is your data set?
      The names have a predetermined order can they be associated with the first available id or is there some constraint?

      Do the two tables have the same number of rows?

       

       

      If the structure is the same as the examples shown, an immediate solution is to sort the two data sets primarily on the "role" fields and secondarily on the other field and then build a table with the two columns "name" and "id".

       

       

       

       


       

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi Anonymous 

         

        Thanks for getting back to me!

         

        In answer to your questions: 

        How big is your data set? 100+
        The names have a predetermined order can they be associated with the first available id or is there some constraint? No constraint, any 'Name' can be associated with any of the correct available 'Roles'

        Do the two tables have the same number of rows? No

         

         

        Unfortunately the structure isn't the same... 😞

         

         

        Thanks again!!!!