Forum Discussion
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;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 |
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
2 Replies
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
