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

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

Remove duplicates from a cell if same after delimiter

9 Replies

Anonymous
Not applicable
4 years ago
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
  Helper V
  4 years ago
  Hello Anonymous ,
  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"
  - Anonymous
    Not applicable
    4 years ago
    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
Helper V
4 years ago
Hello Anonymous ,
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"
- Anonymous
  Not applicable
  4 years ago
  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

Forum Discussion

Remove duplicates from a cell if same after delimiter

9 Replies

Related content

remove duplicate value within a row

Removing Duplicates

How to stop PowerBI from automatically removing duplicates.

Removing duplicates

Remove duplicates bug