cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

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

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

1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
7 REPLIES 7
Solution Sage

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

Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Regular Visitor

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

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?

Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

Helper I

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors