How to Create Random, Unique, Alphanumeric Data in a Field

[SharonHMA]

I need to move some data from one system to another.  The second system requires a Unique ID field that is 6 digits long, alphanumeric.

-my current data comes from two sources and does not have a field with unique values and there is significant enough repetition of fields that I can't be assured that concatenating & selecting something from there won't repeat (unless I'm missing something)

-I can't use row number because I will untimately have more rows than the 6 digits allow - hence the alphanumeric

Can someone advise if and how I can populate a unique field in powerBI for each record?

Thanks

Sharon

[jthomson]

You could try:

- creating an index column numbering each row 1, 2, 3 etc

- creating further columns that effectively convert that number to base 26 (divide the number by various powers of 26, round it down, if the result is more than 26, take off the relevant multiple of 26 so it isn't)

- that'd give you six numbers between 0-25 that you can then switch to letters and concatenate them together

[v-shex-msft · Accepted solution]

HI @SharonHMA,

You can try to use below formula to create guid column, the max count of characters combo is 308,915,776 (26^6).

GUID = UPPER(UNICHAR(RANDBETWEEN(97,122)))&UNICHAR(RANDBETWEEN(97,122))&UNICHAR(RANDBETWEEN(97,122))&UNICHAR(RANDBETWEEN(97,122))&UNICHAR(RANDBETWEEN(97,122))&UNICHAR(RANDBETWEEN(97,122)) 

Regards,

Xiaoxin Sheng

[WLTozon]

Can you help me? I dont understand this function. I am need do a Randbetween, but the number can't repeat.

Example: 

Workers:

Name    ID

João      1

Maria    2

José       3

Product:

Name        ID      RANDBETWEEN(Workers)

Rice           1                      1

Orange      2                      2

Milk           3                      3

Coke          4                      1

......             5                      2

........            6                     3

I have a problem, because the function RandBetween can repeat the same number any times

Can you help me?

Thanks!!

[Sokon]

The big amount of possible values just makes duplicate values extremly unlikely. But it does not really guarantee unique values by design, right? Any idea how to accomplish that?

[v-shex-msft]

Hi @Sokon,

Maybe you can try to enter to query editor and use Text.NewGuid function to create unique guid.

Text.NewGuid

Regards,

Xiaoxin Sheng

[Sokon]

@jthomson: Thanks for the idea! Using that approach I'd lose the random aspect, because knowing the rule, we could restore the original data, right?

@v-shex-msft: Thanks for the hint, too! I didn't know about that function. Yes, with that i should be able to create a list of distinct values i want to anonymize and attach a guid to them. Then I could use the result as a mapping table possible that can be used to replace the original values. I'll give it a try!

Thanks for your help!

[SharonHMA]

Awesome thank you works like a charm!  And yes I know I won't run out with this which is why we chose that for the new system.