Is it possible to remove duplicate numbers from a cell?

[lennox25]

Quick question.

I have duplicate numbers from two columns merged together in Power Query. Can these duplicate numbers be removed from each cell?

[Anonymous]

Hi @lennox25 

Please try this in power query:

= Table.AddColumn(#"Changed Type", "Custom", each Text.Combine(List.Distinct( Text.ToList([Merge]))))

Here I create a set of sample:

The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lennox25]

Hi @Anonymous  I have tried this and it fails when it goes past 9 so from 10 onwards it will remove as it thinks is a 1 and just will leave a 0, for say 11 it will remove as there is a 1 there. Any ideas on how to resolve?

[Anonymous]

Hi @lennox25 

How does your data distinguish between 10 and 1,0, is there a space in between.

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi @lennox25 

Please try this:

= Table.AddColumn(Source, "Custom", each Text.Combine(List.Distinct( Text.Split([Merge]," "))," "))

The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lennox25]

Hi, no there is not, its 1, 10 for example

[lennox25]

Thank you @Anonymous   I will give this a try 🙂

[Shivu-2000]

Hi @lennox25 
Can you provide me dummy data and file.

[lennox25]

Hi @Shivu-2000 sorry for the delay I had to create a new report removing the senstive info.

Link here https://drive.google.com/file/d/1-2imAhCyfFxoR5xH5aSeBs7egEG8ZAbe/view?usp=sharing 

Query  -Merge 1 and the Merged Column is where the duplicate numbers need removing from each row 

[Shivu-2000]

Hi @lennox25 
You have only send me the pbix file not the data.
I need data so that I can open it in power query as when I am trying to open it, it is showing me errors as:

[lennox25]

Hi @Shivu-2000  please see links - TIA

https://docs.google.com/spreadsheets/d/1na_c-xNwAGJmbCM6_-I0YbJgUcj3UVV7/edit?usp=sharing&ouid=10762...   https://docs.google.com/spreadsheets/d/1lVa0RObIcEHDfyls42fA0rewGj4gMdhz/edit?usp=sharing&ouid=10762... 

[Ashish_Mathur]

Hi,

Why is the answer 3 in cell C6?  Shouldn't the answer be 2,0,4,5?

[lennox25]

2045 is the store number

[Shivu-2000]

Hi @lennox25 
Do give me the edit access to the sheets as I have to use them in the report.

[lennox25]

@Shivu-2000  Done for you

[Shivu-2000]

Hi @lennox25 
You can try any of these method accordingly:
1. Using List.Distinct:

If your data is stored as a list within each cell:

• Select the column containing the merged data.
• In the formula bar, enter the following M code:

Table.TransformColumns(
  Source,
  {{"Column Name", value => List.Distinct(value)}}  // Replace "Column Name" with your actual column name
)

2. Splitting, Removing Duplicates, and Joining:

If your data is not stored as a list:

• Select the column containing the merged data.
• In the formula bar, enter the following M code:

let
  // Replace "Delimiter" with the character separating your numbers (e.g., comma)
  splitValues = Text.Split(Text.Trim([Column Name]), ",") ,  // Replace "Column Name" with your actual column name
  distinctValues = List.Distinct(splitValues),
  joinedValues = Text.Combine(distinctValues, ",")
in
  Table.TransformColumns(Source, {{[Column Name], joinedValues}})

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Happy to help!

[lennox25]

Hi @Shivu-2000 , Is is the 2nd option, but Im not sure how to enter in formula bar, do I need to add a step?

[Shivu-2000]

Hi @lennox25 
https://www.youtube.com/watch?v=9dX9PK_KlSA
This might be helpful.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Happy to help!

[lennox25]

@Shivu-2000  thanks for your time. Apologies Im even more confused. This is what I am trying to acheive.

Cell example = 1 2 3 4 5 1 

Looking at the above there are 2 '1's  - I need to remove the additional '1' so that its just '1 2 3 4 5'.