Forum Discussion
Nipius
Advocate I
Advocate ICreating unique value if value already exists
Hi all!
I have a query including a column with duplicate values. For the purpose of my table, I'd like to add another column where I add another character (for example "A" or "1") to the duplicate of a value.
For example, in case of
58379
58379
58379
58379
93850
39412
39412
I'd like the first value to keep as is, but change the other duplicates and ignore the unique values. The above would then result in
58379
58379A
58379AA
58379AAA
93850
39412
39412A
Is there a way for me to accomplish this? Thanks in advance!
9 Replies
- parry2k
Super User
Nipius copy these steps into your table using query editor.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrUwNrdUitUhxLI0tjA1ALOMLU0MjZBZsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Id = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Id"}, {{"Count", each Table.AddIndexColumn(_, "Rank", 0)}}), #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Rank"}, {"Rank"}), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Count",{{"Rank", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type1", "New Id", each Number.ToText([Id]) & ( if [Rank] > 0 then "-" & Number.ToText([Rank]) else "" )), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Id", "Rank"}) in #"Removed Columns"
