Create 3 digit sequential unique key

[jwb3d]

I am new to Power BI. Currently, I have a key routine in MS Access that I am attempting to replicate in Power BI. I need to generate a sequential three-digit key for each row in a table. I am open to suggestions and hope that the example below will be helpful.

Example

keySet = Split("0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,G,H,J,K,L,M,N,P,Q,R,S,T,U,V,W,X,Y,Z", ",")

KEY = 1CY

Begin with the third character in the key.

Search for the next available character in the keyset to assign to the third position. If found, replace Y with Z. FINISH.
Result: 1CZ.

If not found, the current column is "Z". Proceed to the second character in the key. You are looking for the next available character in the keyset to assign to the second position.

If found, replace C with D and set the third character in the third position to 0. Result: 1D0.

If not found, the current column is "Z". Move to the first character in the key. You are looking for the next available character in the keyset to assign to the first position.

If found, replace 1 with 2 and set the third character in the second position to 0. Result: 20Y.

[Juan-Power-bi · Accepted solution]

the best way is with Power query , so your idea: your keyset has 34 characters (0-9 plus letters minus I and O). So each row just needs a sequential number converted to base-34. Add an index column in Power Query (0, 1, 2...), then convert that number to base-34 using your keyset, and pad to 3 digits.
Something like this in Power Query as a custom column:
mlet
keyset = {"0","1","2","3","4","5","6","7","8","9","A","B","C","D","E","F","G","H","J","K","L","M","N","P","Q","R","S","T","U","V","W","X","Y","Z"},
n = [Index],
d1 = Number.IntegerDivide(n, 34*34),
d2 = Number.IntegerDivide(Number.Mod(n, 34*34), 34),
d3 = Number.Mod(n, 34)
in
keyset{d1} & keyset{d2} & keyset{d3}
That gives you 000 through ZZZ 

Hope it help !! 🙂

[ThxAlot · Accepted solution]

Hard to full understand your explanation about coding logic; but here's an elegant way to build Base10, Base16 ... sytem

let
    UDF_Digit = (digits as list, bits) =>
        if bits<=1 then digits else List.TransformMany(digits, each @UDF_Digit(digits, bits-1), (x,y) => x&y),
    Source = fx_Digit({"0".."9","A".."F"}, 3)
in
    Source

[jwb3d]

I would appreciate the ability to set a starting key, as some of the keys are already in use. I would like to have the ability to set a starting key. For example:

In the database, keys 001-003 are already taken. I have captured the last used key, 003, in a separate column, and I would like to input this 'last used key' into the query. Consequently, when the query initiates, it will assign 004 to the next row. Thank you.

[Juan-Power-bi · Accepted solution]

the best way is with Power query , so your idea: your keyset has 34 characters (0-9 plus letters minus I and O). So each row just needs a sequential number converted to base-34. Add an index column in Power Query (0, 1, 2...), then convert that number to base-34 using your keyset, and pad to 3 digits.
Something like this in Power Query as a custom column:
mlet
keyset = {"0","1","2","3","4","5","6","7","8","9","A","B","C","D","E","F","G","H","J","K","L","M","N","P","Q","R","S","T","U","V","W","X","Y","Z"},
n = [Index],
d1 = Number.IntegerDivide(n, 34*34),
d2 = Number.IntegerDivide(Number.Mod(n, 34*34), 34),
d3 = Number.Mod(n, 34)
in
keyset{d1} & keyset{d2} & keyset{d3}
That gives you 000 through ZZZ 

Hope it help !! 🙂

[jwb3d]

I want to express my gratitude for this custom query. It functioned as intended. I would appreciate the ability to set a starting key, as some of the keys are already in use. I would like to have the ability to set a starting key. For example:

In the database, keys 001-003 are already taken. I have captured the last used key, 003, in a separate column, and I would like to input this 'last used key' into the query. Consequently, when the query initiates, it will assign 004 to the next row. Thank you.