Remove duplicates from a cell if same after delimiter

[Junaid11]

Hello,

I have columns which from which I want to remove duplicates value. As shown in below picture if after delimeter the character is same it should be removed else it should not like if there is Green, Green then only one should remain in the cell and if there are two colour both colours should be shown. Kindly help me get this done.

I have used below formula to get the columns value after grouping to turned in to list.

Table.Column([Check],"Revised RAG")

Thank you

[Junaid11]

Hello @Vera_33 ,

I have created custom columns based on list already and my code looks like below. How should I add your code for each custom column so it does not repeat the same value after comma. Kindly have a look at the below code that I have right now. Kindly if possible tell the code to be used or for each custom column that I have so that I do not get repetition.

Thank you

let
Source = Table.Combine({All, Table.RenameColumns(Data,{{"Person ID","Person ID"},{"Vendor ID","Vendor ID"}})}),
#"Removed Duplicates" = Table.Distinct(Source),
#"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"Person ID", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"Person ID", "Name", "Vendor ID", "Start Date", "End Date"}, {{"Check", each _, type table [Person ID=nullable text, Name=nullable text, Vendor ID=nullable text, Vendor Description=nullable text, Start Date=nullable date, End Date=nullable date, #"Case MASH'd?"=nullable text, Time Taken=nullable number, #"In Time?"=nullable text, Initial RAG=nullable text, Revised RAG=nullable text, wfl Next Step Reason=nullable text, Subject ID=nullable text, Match=nullable number]}}),
#"Sorted Rows1" = Table.Sort(#"Grouped Rows",{{"Person ID", Order.Ascending}}),
#"Added Custom" = Table.AddColumn(#"Sorted Rows1", "Initial RAG", each Table.Column([Check],"Initial RAG")),
#"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Initial RAG", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Added Custom1" = Table.AddColumn(#"Extracted Values", "Revised RAG", each Table.Column([Check],"Revised RAG")),
#"Extracted Values1" = Table.TransformColumns(#"Added Custom1", {"Revised RAG", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Added Custom2" = Table.AddColumn(#"Extracted Values1", "wfl Next Step Reason", each Table.Column([Check],"wfl Next Step Reason")),
#"Extracted Values2" = Table.TransformColumns(#"Added Custom2", {"wfl Next Step Reason", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Filtered Rows" = Table.SelectRows(#"Extracted Values2", each true),
#"Added Custom3" = Table.AddColumn(#"Filtered Rows", "Case MASH'd?", each Table.Column([Check],"Case MASH'd?")),
#"Extracted Values3" = Table.TransformColumns(#"Added Custom3", {"Case MASH'd?", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Added Custom4" = Table.AddColumn(#"Extracted Values3", "Time Taken", each Table.Column([Check],"Time Taken")),
#"Extracted Values4" = Table.TransformColumns(#"Added Custom4", {"Time Taken", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Added Custom5" = Table.AddColumn(#"Extracted Values4", "In Time?", each Table.Column([Check],"In Time?")),
#"Extracted Values5" = Table.TransformColumns(#"Added Custom5", {"In Time?", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Added Custom6" = Table.AddColumn(#"Extracted Values5", "Subject ID", each Table.Column([Check],"Subject ID")),
#"Extracted Values6" = Table.TransformColumns(#"Added Custom6", {"Subject ID", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Added Custom7" = Table.AddColumn(#"Extracted Values6", "Match", each Table.Column([Check],"Match")),
#"Extracted Values7" = Table.TransformColumns(#"Added Custom7", {"Match", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Removed Columns" = Table.RemoveColumns(#"Extracted Values7",{"Check"})
in
#"Removed Columns"

[Vera_33]

Hi @Junaid11 

So you don't know how to use the code? Can you duplicate your current query, and go to Advanced Editor to replace with all the code here, then modify the steps accordingly in the GUI?

let
Source = Table.Combine({All, Table.RenameColumns(Data,{{"Person ID","Person ID"},{"Vendor ID","Vendor ID"}})}),
#"Removed Duplicates" = Table.Distinct(Source),
#"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"Person ID", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"Person ID", "Name", "Vendor ID", "Start Date", "End Date"}, {{"Check", each _, type table }}),
#"Sorted Rows1" = Table.Sort(#"Grouped Rows",{{"Person ID", Order.Ascending}}),
AddInitialRAG = Table.AddColumn(#"Sorted Rows1", "Initial RAG", each Text.Combine( List.Distinct( List.Transform([Check][Initial RAG], each Text.Trim(Text.From(_))),",")),
AddRevisedRAG = Table.AddColumn(AddInitialRAG, "Revised RAG", each Text.Combine( List.Distinct( List.Transform([Check][Revised RAG], each Text.Trim(Text.From(_))),",")),
Addwfl = Table.AddColumn(AddRevisedRAG, "wfl Next Step Reason", each Text.Combine( List.Distinct( List.Transform([Check][wfl Next Step Reason], each Text.Trim(Text.From(_))),",")),
AddCase = Table.AddColumn(Addwfl, "Case MASH'd?", each Text.Combine( List.Distinct( List.Transform([Check][Case MASH'd?], each Text.Trim(Text.From(_))),",")),
AddTimeTaken = Table.AddColumn(AddCase, "Time Taken", each Text.Combine( List.Distinct( List.Transform([Check][Time Taken], each Text.Trim(Text.From(_))),",")),
AddInTime = Table.AddColumn(AddTimeTaken, "In Time?", each Text.Combine( List.Distinct( List.Transform([Check][In Time?], each Text.Trim(Text.From(_))),",")),
AddID = Table.AddColumn(AddInTime, "Subject ID", each Text.Combine( List.Distinct( List.Transform([Check][Subject ID], each Text.Trim(Text.From(_))),",")),
AddMatch = Table.AddColumn(AddID, "Match", each Text.Combine( List.Distinct( List.Transform([Check][Match], each Text.Trim(Text.From(_))),","))

in
AddMatch

[Vera_33]

Hi @Junaid11 

Is it what you want? You may have space like the screenshot, right? All proper, no different cases you need to take care?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci9KTc3TCUpNUYrVgfHAJIKvABOIBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine( List.Distinct(List.Transform( Text.Split([Column1],","), Text.Trim)),","))
in
    #"Added Custom"

[Junaid11]

Hello @Vera_33 ,

I have created custom columns based on list already and my code looks like below. How should I add your code for each custom column so it does not repeat the same value after comma. Kindly have a look at the below code that I have right now. Kindly if possible tell the code to be used or for each custom column that I have so that I do not get repetition.

Thank you

let
Source = Table.Combine({All, Table.RenameColumns(Data,{{"Person ID","Person ID"},{"Vendor ID","Vendor ID"}})}),
#"Removed Duplicates" = Table.Distinct(Source),
#"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"Person ID", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"Person ID", "Name", "Vendor ID", "Start Date", "End Date"}, {{"Check", each _, type table [Person ID=nullable text, Name=nullable text, Vendor ID=nullable text, Vendor Description=nullable text, Start Date=nullable date, End Date=nullable date, #"Case MASH'd?"=nullable text, Time Taken=nullable number, #"In Time?"=nullable text, Initial RAG=nullable text, Revised RAG=nullable text, wfl Next Step Reason=nullable text, Subject ID=nullable text, Match=nullable number]}}),
#"Sorted Rows1" = Table.Sort(#"Grouped Rows",{{"Person ID", Order.Ascending}}),
#"Added Custom" = Table.AddColumn(#"Sorted Rows1", "Initial RAG", each Table.Column([Check],"Initial RAG")),
#"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Initial RAG", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Added Custom1" = Table.AddColumn(#"Extracted Values", "Revised RAG", each Table.Column([Check],"Revised RAG")),
#"Extracted Values1" = Table.TransformColumns(#"Added Custom1", {"Revised RAG", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Added Custom2" = Table.AddColumn(#"Extracted Values1", "wfl Next Step Reason", each Table.Column([Check],"wfl Next Step Reason")),
#"Extracted Values2" = Table.TransformColumns(#"Added Custom2", {"wfl Next Step Reason", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Filtered Rows" = Table.SelectRows(#"Extracted Values2", each true),
#"Added Custom3" = Table.AddColumn(#"Filtered Rows", "Case MASH'd?", each Table.Column([Check],"Case MASH'd?")),
#"Extracted Values3" = Table.TransformColumns(#"Added Custom3", {"Case MASH'd?", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Added Custom4" = Table.AddColumn(#"Extracted Values3", "Time Taken", each Table.Column([Check],"Time Taken")),
#"Extracted Values4" = Table.TransformColumns(#"Added Custom4", {"Time Taken", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Added Custom5" = Table.AddColumn(#"Extracted Values4", "In Time?", each Table.Column([Check],"In Time?")),
#"Extracted Values5" = Table.TransformColumns(#"Added Custom5", {"In Time?", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Added Custom6" = Table.AddColumn(#"Extracted Values5", "Subject ID", each Table.Column([Check],"Subject ID")),
#"Extracted Values6" = Table.TransformColumns(#"Added Custom6", {"Subject ID", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Added Custom7" = Table.AddColumn(#"Extracted Values6", "Match", each Table.Column([Check],"Match")),
#"Extracted Values7" = Table.TransformColumns(#"Added Custom7", {"Match", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Removed Columns" = Table.RemoveColumns(#"Extracted Values7",{"Check"})
in
#"Removed Columns"

[Vera_33]

Hi @Junaid11 

I only add one custom column, if the list already has all text, otherwise wrap a Text.From

let
Source = Table.Combine({All, Table.RenameColumns(Data,{{"Person ID","Person ID"},{"Vendor ID","Vendor ID"}})}),
#"Removed Duplicates" = Table.Distinct(Source),
#"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"Person ID", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"Person ID", "Name", "Vendor ID", "Start Date", "End Date"}, {{"Check", each _, type table }}),
#"Sorted Rows1" = Table.Sort(#"Grouped Rows",{{"Person ID", Order.Ascending}}),
#"Added Custom" = Table.AddColumn(#"Sorted Rows1", "Initial RAG", each Text.Combine( List.Distinct( List.Transform([Check][Initial RAG]), Text.Trim)),",")

in
#"Added Custom"

[Junaid11]

Hello @Vera_33 ,

After putting your formula I am getting the below error.

I am new to Power Query kindly bear with me.

Can you please have a look at it. If you want I can provide you the file of Data source.

Thanks

[Vera_33]

Hi @Junaid11 

My bad, I typed 2 errors...modified the original one...

Text.Combine( List.Distinct( List.Transform([Check][Initial RAG], Text.Trim)),",")

[Junaid11]

Hello @Vera_33 ,

It is working perfectly fine with Text columns. What should I do if the column has dates or numbers in it?

[Vera_33 · Accepted solution]

Hi @Junaid11 

Wrap a Text.From

Text.Combine( List.Distinct( List.Transform([Check][Initial RAG], each Text.Trim(Text.From(_))),",")