How to create a random number column from a defined range with unique values

[DJSingh]

Background:

I have a Sharepoint list. I have a Power App which uses a counter to pick the value from the fields in the Sharepoint. I use the Lookup formula: 

LookUp(MyDataSourse,Number=varCounter, Answer)

I have a Number column which increments at every new row I add manually in my list. I increment the varCounter by 1.

Problem:

Currently my way is boring and very predictable of which value will be picked next. I want to randomise it but do not know how to.

I was thinking of some way to use an 'Array' type or something similar, and populate it in such a way such that if my list has 50 rows, I populate that 'array' or similar supported variable with 50 number entries, all unique. Then I just need to keep reading the first one and then delete that entry, and keep repeating it until no value is left in the variable.

Can such a thing be achieved in power apps? if then please help with some code pointers as to how? 

Thanks

[cdwhite]

You might be able to pick the bones out of a recent post I’ve done about generating unique ID’s with Power Fx. Perhaps something here that could help you randomise within range(s) of your choice:

https://platformsofpower.net/generate-a-unique-id-with-power-fx/

alternatively, look into the RandBetween function as that might also be good for you:

https://learn.microsoft.com/en-us/power-platform/power-fx/reference/function-rand

[madlad · Accepted solution]

The following code will return a collection with the numbers 0-50 in a random order. You should be able to base your solution off of this

ClearCollect(randVars, Sequence(50));
ClearCollect(randVars, Shuffle(randVars))

Hope this helps!

[Anonymous_Hippo]

Hello @DJSingh ,

Yes this can be done 🙂 Here is one idea that come to mind

1) In the onStart of your app add this code

ClearCollect(MyCollection, MyDS.Number) //creates a collection from your data source with all the numbers you want to pick from

2) Now we basically want to pick a random index number from your collection

use Rand() fucntion to pick a random index from the collection, look up the corresponding record in your data source, and then remove the number from the collection.

Set(varRandomIndex, RoundDown(Rand() * CountRows(MyCollection), 0));//randomly picked index from the collection
Set(varRandomNumber, LookUp(MyCollection, Value = MyCollection[varRandomIndex]));//number at the randomly picked index
Set(varRandomRecord, LookUp(MyDS, Number = varRandomNumber));//removes the randomly picked number from the collection so it won't be picked again
Remove(MyCollection, varRandomNumber);

Now extra notes !

Note that Rand() fucntion returns a random decimal between 0 (inclusive) and 1 (exclusive), so  Rand()*CountRows(MyCollection) gives a random decimal between 0 and the number of records in the collection. Rounddown() is used to convert this to an integer that can be used as an index.

This should give you a random record from your data source each time you run the code, without repeating records until you've gone through all of them.

Cheers!

__PRESENT