How to remove duplicates from a list column in my report?

[Yonas]

Hi all, hope you are staying safe. I have a column in my powerbi report dataset which holds list values in a column. 

Id       Name

1        kevin,yona,rachel,kevin

2        bruce,miller,kim

3        adam,rita,adam,adam

As you can see there are duplicate values in the list on Name column. I wanted to write a query which will remove those duplicates and and keep one occurences. the result set i want is like this

Id       Name

1        yona,rachel,kevin

2        bruce,miller,kim

3        adam,rita

Any ideas? thanks

[Greg_Deckler]

I did this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Hck7CoBADIThu6SeRj1O2CKuAcO+IKjg7V3TDD/zMdNCoKKPdbyjC1zyqRXxUALTOn33Oyua1aqOYi1gmyCHNLhdgqh/KKUP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Id = _t, Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Name", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Name", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Name.1", "Name.2", "Name.3", "Name.4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Name.1", type text}, {"Name.2", type text}, {"Name.3", type text}, {"Name.4", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Id"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns")
in
    #"Removed Duplicates"

[Yonas]

Thanks Greg, Just to clarify about my case, The Name's list might be many(not constant) for each Id instead of three. it might be two, four or eight, the data might be like this

Id         Name

1          kevin,yona,kevin

2          jim,rachel,jim,bruce,cris,davis

How can i put this on this part of query 

#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Name", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Name.1", "Name.2", "Name.3", "Name.4"}),

and I have two columns called "Service" and "Line". How can i include them during unpivoting?

#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Id"}, "Attribute", "Value")

Thanks

[v-juanli-msft · Accepted solution]

Hi @Yonas 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Hck7CoBADIThu6SeRj1O2CKuAcO+IKjg7V3TDD/zMdNCoKKPdbyjC1zyqRXxUALTOn33Oyua1aqOYi1gmyCHNLhdgqh/KKUP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Id = _t, Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Name", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Name", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Name"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Name", type text}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type1"),
    #"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Id"}, {{"Data", each _, type table [Id=number, Name=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column([Data],"Name")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Data"})
in
    #"Removed Columns"

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.