Combine data and number column in Power Query

Shelley-Baynton 

Hope I'm understanding correctly.  You are trying to make a key field you can use to remove duplicates.

If this is correct this is what I do in these situations:

Clean up the data 1st then make your key field.

Since i don't want to lose my data, I'll start of by creating duplicates of the columns I'm trying to convert into a key field and do my cleaning on those columns especially since the column merge will remove the columns being merged, this means I only lose the duplicate columns not the originals.

Here are the steps:

• Right clicking each column header and select Duplicate Column
  
  
  • (No need to rename these new columsn since these will be throw away columns)
• In order to remove leading and trailing spaces from the data, select the 2 newly copied columns
  • While they are highlighted right click on the header of one of them --> Change Type --> Text
    
  • While they are still highlighted, if not reselect them, right click on the header of one of them --> Transform --> Trim
    
  • You could filter, sort and replace through the copied columns to clean up the data but since you are just looking to remove duplicates, you could skip this and come back to it once your data set is smaller after deduping, making it a little quicker to clean up.
• Finally Create your key by selecting both copied columns again, if they are not already selected, and right clicking on the header of one them, one more time, then select Merge Columns
  
  

  • Choose a seperator if you want to add one and name your new column

    

     

• This will create your new key field that can be used to remove duplicates.  One way I remove duplicates is using the group by option.
  

Hope this helps.