Forum Discussion
Create 3 digit sequential unique key
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 ZZZHope it help !! 🙂
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
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 !! 🙂
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.
