Calculated column to avoid duplicate UID

[unnijoy]

I got a table with ID, Year , UID. Below is the sample table.

UID	Year	ID	Fina ID
202023	2020	23	202023
202023	2020	23
202023	2020	23
202023	2020	23
202124	2021	24	202124
202124	2021	24
202125	2021	25
202125	2021	25	202125

 Now i need to create a new column ("Final ID"). The issue is that if the UID is repeating then that UID should come only once in the new Column.
Can anyone suggest and idea.

[v-henryk-mstf]

Hi @unnijoy ,

Try like below:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIAQmMlHTADRBkrxepQT9jQyAQibAiiTAgKmyIJm+IUjgUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, Category = _t, Year = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}, {"Category", type text}, {"Year", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Country", "Category"}, {{"Rows", each _, type table [Country=text, Category=text, Year=number, Value=number]}}),
Indexed = Table.TransformColumns(#"Grouped Rows", {{"Rows", each Table.AddIndexColumn(_,"GroupIndex", 1, 1)}}),
#"Expanded Rows" = Table.ExpandTableColumn(Indexed, "Rows", {"Year", "GroupIndex"}, {"Year", "GroupIndex"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Rows",{{"Country", "UID"}, {"Year", "ID"}, {"Category", "Year"}})
in
    #"Renamed Columns"

Final ID = IF(Table1[GroupIndex] = "1",Table1[UID],BLANK())

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[unnijoy]

@v-henryk-mstf ,  Mine is a calculated table. so it won;t come in Query Editor... Can you tell me how i can add the group index column.

[unnijoy]

In excel we can do it by Final ID=IF(A1=C1,"",A1). But how can we do that with Dax.