Forum Discussion

han_rj's avatar
han_rj
Helper IV
4 months ago
Solved

remove duplicate value within a row

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;Pa...
Need Help
  • jgeddes's avatar
    jgeddes
    4 months ago

    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
jgeddes's avatar
jgeddes
Super User
4 months ago

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