Combine data and number column in Power Query

[Shelley-Baynton]

Hello,

I need to some help, I think it should be fairly simple but for the life of me I can't find the solution. I have some not so great data which I need to find the duplicates, to do that I need to combine a number column with a data column to group by and see the duplicate items. 

Number Column	Date	Ref
0001	16/08/2021	0001 16/08/2021

Unfortunately I just keep getting an error message so I'm clearly doing something wrong.

Any help would be mightly appreciated. 

[Syndicate_Admin]

@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.

[LarryAlx]

@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.

[mahoneypat · Accepted solution]

FYI that you can Ctrl click on your number and date columns to select them both, and then choose Remove Duplicates from the ribbon under Remove Rows.  To concatenate them, they both need to be type text, so you'll need something like:

= Number.ToText([Number Column]) & " " & Date.ToText([Date Column], "yyyyMMMdd")

Pat

[goncalogeraldes]

As @sean_w said it is quite hard to give a straight answer without a little bit more of context. My suggestion would be to, prior to the combine step in the Query Editor, changing the type of the two columns to "type any" and then combining the two. After the combine is performed, changing back to "date" and "Int64.Type" (i guess its this type you have now) to maintain data consistency and quality.

Hope this answer solves your problem! If you need any additional help please tag me in your reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️ or give it a kudoe 👍
Thanks!

Best regards,
Gonçalo Geraldes

[sean_w]

Without seeing the data types, how you're trying to combine the data, and the error, this is a bit of a shot in the dark.  Given you're dealing with two different data types, it's likely that you're attempting to use a function that doesn't handle the data types.  Here are a couple resources that might be helpful:

Power Query Merge Columns help article:
https://support.microsoft.com/en-us/office/merge-columns-power-query-80ec9e1e-1eb6-4048-b500-d5d42d9...

Power Query Function references:

https://docs.microsoft.com/en-us/powerquery-m/number-totext

https://docs.microsoft.com/en-us/powerquery-m/date-totext

[Shelley-Baynton]

Hi Sean,

Thanks for the feedback - @mahoneypat has managed to provide a solution 😊