This is a follow-up trend,
Example, how would one generate a unique key which can be in any random 8digits number as passcode, for a given student if their School, StudentID, StudentNames, their Class are in this order
when there is a different values for School, StudentID, StudentNames, Class , it generate a different passcode for subsequent student, so that no students have thesame passcode or the 8 randomly generated numbers.
Thank you.
You can try Number.RandomBetween to generate numbers (result should be rounded as it may have decimals)
Number.RandomBetween(10000000, 99999999)
You can also use your numbers:
Number.RandomBetween(10000000 + School + StudentID, 99999999)
Numbers are random, but there is a chance to find duplicates, you should add steps to count the occurrence of each number to make sure it's 1.
HOW PLEASE?
this is the challenge i am having, and have uploaded it as an attachement ?
This is a follow up, to my previous question
"Generate a unique passcode accross records in powerquery@
Please i have added a customIndex column, to generate the unique passcode, but whenever, i refresh powerquery the values, keep refreshing, whic i i dont want, please, how do you help me so that, no matter the refresh the unique passcode of 8 digit number remains intact for every studentId
This is the attachment.
Instead of trying to generate a specific id in a predefined structure, why not using Text.NewGuid() function?
Attached is a demo for a way to add a guid only to students that don't have one already.
If you add a new student into accreditation table, when you refresh the Table_StudentIDList table, only the new student will receive a new guid, all others will keep the existing guid from same table.
If you still prefer to generate your own random numbers, which can generate duplicates, replace in query:
= Table.TransformColumns(#"Removed Duplicates",{{"GUID", each if _=null then Text.NewGuid() else _, type text}})
with :
= Table.TransformColumns(#"Removed Duplicates",{{"GUID", each if _=null then Number.RandomBetween(10000000, 99999999) else _, type text}})
Thank you, so there is no way to make it generate just 8 or 10 digit number GUI while still ensuring uniqueness of the number generated?
Secondly, whenever i tried to open the excel document in powerquery mode, this is what i get
Formula.Firewall: Query 'Table_StudentIDList' (step 'GUID') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
Here is a version that generates numbers using Number.RandomBetween.
After numbers are generated, there is a new step that checks the generated ID's for duplicates, that step calls a recursive function CheckID. That function calls itself until the random function generates an ID that is not in the list already.
There is a very small chance for the function to fail, but only if the call stacks exceeds the max limit. Each recursion level is stored in memory, so the limit of recursive calls depends on PC configuration.
From PQ Editor window, go to File> Options and Settings>Query Options>Global - Privacy and check the Ignore Privacy Levels. You don't have external data, all data is in the file so privacy is useless.
Thank you @Catalin, sincerely this works like magic!
