remove duplicate value within a row

[han_rj]

Hi Team,

Need help with a solution that can remove duplicate values within a row in column product.

Before

ID	Name	Product	Location
1	Able	Can;Tin;Tin;Can	100
2	Sam	Can;Pan;Pan;Can;Bin	101
3	San	Pole;Bin;Pin;Bin	102

After

ID	Name	Product	Location
1	Able	Can;Tin	100
2	Sam	Can;Pan;Bin	101
3	San	Pan;Bin	102

[v-priyankata]

Hi @han_rj 

Thank you for reaching out to the Microsoft Fabric Forum Community.

@jgeddes Thanks for the inputs.

I hope the information provided by user was helpful. If you still have questions, please don't hesitate to reach out to the community.

[jgeddes · Accepted solution]

Add this line of code to your query

= Table.TransformColumns(previousQueryStep, {{"Product", each Text.Combine(List.Distinct(Text.Split(_, ";")), ";"), type text}})

Full example code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJMykkFUs6JedYhmRAMZANFDA0MlGJ1opWMgOzgxFyomgAoBrGdMiHqDMHqjMHqQCIB+TmpIEnrgEyEIiOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t, Product = _t, Location = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}, {"Product", type text}, {"Location", Int64.Type}}),
    Custom1 = Table.TransformColumns(#"Changed Type", {{"Product", each Text.Combine(List.Distinct(Text.Split(_, ";")), ";"), type text}})
in
    Custom1