Forum Discussion

SharonHMA's avatar
SharonHMA
Helper I
9 years ago
Solved

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

 

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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

    • SharonHMA's avatar
      SharonHMA
      Helper I
      9 years ago

      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.

    • Sokon's avatar
      Sokon
      Advocate V
      8 years ago

      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?

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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

    • WLTozon's avatar
      WLTozon
      Regular Visitor
      8 years ago

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

  • jthomson's avatar
    jthomson
    Solution Sage
    8 years ago

    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