Skip to main content
cancel
Showing results for 
Search instead 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

Reply
SharonHMA
Helper I
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
v-shex-msft
Community Support
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)) 

2.PNG

 

Regards,

Xiaoxin Sheng

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

View solution in original post

7 REPLIES 7
jthomson
Solution Sage
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

v-shex-msft
Community Support
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)) 

2.PNG

 

Regards,

Xiaoxin Sheng

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

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?

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!

 

Thanks for your help!

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.

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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