Create key column based on another column

[Anonymous]

Hello,

I have a table with a key column ("account_id"), however the number of digits in it is very large.

Do you know how to create a custom column where I can get the result below ("added_custom" column):

Thank you!

[Vijay_A_Verma]

What logic to be followed to arrive at those numbers? 

[Anonymous]

For "added_custom" column numbers?

Basically a rank column logic. It started at number 1 (for example) and each time the account_id was repeated the power query had to indicate the number already created previously.
If the account_id is new then add +1 to the last number created.

[Vijay_A_Verma · Accepted solution]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0ABEmSrE60UpOIKY5iDAE88HylhBBEN8ZxDQDEaYI9SZAwsgAwQebZwzmu0CkwJpiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [account_id = _t, quantity = _t, unit_price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"account_id", type text}, {"quantity", Int64.Type}, {"unit_price", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"account_id"}, {{"All", each _, type table [account_id=nullable text, quantity=nullable number, unit_price=nullable number, Index=number]}}),
    #"Added Index1" = Table.AddIndexColumn(#"Grouped Rows", "Rank", 1, 1, Int64.Type),
    #"Expanded All" = Table.ExpandTableColumn(#"Added Index1", "All", {"quantity", "unit_price", "Index"}, {"quantity", "unit_price", "Index"}),
    #"Sorted Rows" = Table.Sort(#"Expanded All",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns"