Forum Discussion
How to get unique column
Hello,
I need to make a unique column to get a 1-to-1 relation with another column. I have the table below:
| Header |
| A |
| A |
| B |
| B |
And the table I need is:
| Header | Unique |
| A | A1 |
| A | A2 |
| B | B1 |
| B | B2 |
3 Replies
AlbertPietens it will make more sense to do it in PQ, let me know if that is something you would like to do.
parry2k Power query is fine. Ho should I get this using power query?
AlbertPietens start a new blank query, click advanced editor, and copy this code. You can follow these steps in your data.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YGRTjAyFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Header = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Header", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Header"}, {{"All", each Table.AddIndexColumn(_, "Rank", 1)}}), #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Rank"}, {"Rank"}), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded All",{{"Rank", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type1", "Unique", each [Header] & [Rank], type text), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Rank"}) in #"Removed Columns"
