topic Re: Create 3 digit sequential unique key in DAX Commands and Tips

Create 3 digit sequential unique key

jwb3d — Sat, 11 Apr 2026 20:29:21 GMT

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.

Re: Create 3 digit sequential unique key

Juan-Power-bi — Mon, 13 Apr 2026 14:21:22 GMT

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 !! 🙂

Re: Create 3 digit sequential unique key

jwb3d — Mon, 13 Apr 2026 21:06:04 GMT

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.

Re: Create 3 digit sequential unique key

ThxAlot — Mon, 13 Apr 2026 22:21:30 GMT

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

Re: Create 3 digit sequential unique key

jwb3d — Tue, 14 Apr 2026 11:53:32 GMT

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: