Power Query non volatile random list

Your requirement really leads me to learn a new thing, so thanks to you first of all.

To generate a list of numbers that looks random but remain the same non volatile when you refresh your query, I found a technique that I was not aware about it, which may achieve the fixed random sequences in power query after refresh in all cases.

It is called a Linear Congruential Generator (LCG), of course it is not a native built-in in power query but i mean to apply it in power query context.

The idea is simple:

1. Start with a Fixed Number.
2. Multiply by a Fixed Factor
3. Add a Fixed Increment
4. Divide and Keep the Remainder
5. That Remainder Is the Next Fixed Number
6. Repeat the Process

We used 4 factors to apply this technique:

• seed or starting point, which is #1
• multiplier, which is #2
• increment, (they used to call it modulus), and this one affect the randon numbers which is #3
• modulus, which is #4.

Other items we used of numbers you want in the list are :
lowerBound: lower or beginner
upperBound: maximum or end

So, now we have 4 fixed numbers or items:
seed or starting point: This is the starting value and is not directly shown in the final list.
multiplier: 1103515245, they are recommending to use this value for it and many opinions says it is a proven value, but honestly i was not able to figure out why this one in particular.
increment: this is any random number you pick, but you need to fix it.
modulus: after doing the multiplication and addition, you divide by the modulus and keep the remainder.

So we follow this:
Starting with the Seed
Use the function to generating the First Number
Building the Sequence
Removing the Original Seed

Now lets move to power query part:
I created a table in excel to include all parameters as follow:
Notes are not my words, but I copied it from the paper I've explored to learn the concept, so I thought to copy it in full so you can understand it better.
Excel 2016 (Windows) 64 bit

	A	B	C	D
1	Linear Congruential Generator
2	Factor	Vaue	Note
3	Seed	123,456,789	Fixed	This is your fixed starting value. The LCG algorithm uses this value to begin the sequence generation. It isn’t directly part of the final output but determines the entire sequence.
4	Multiplier	1,103,515,245	Fixed	This number is multiplied by the current value at each step. Its proven effectiveness (and widespread use) ensures that when combined with the other parameters, the generated numbers have good distribution.
5	Increment	12,345	Fixed	After multiplying by the multiplier, this fixed number is added to help prevent the sequence from becoming too regular or cyclic too quickly.
6	Modulus	2,147,483,648	Fixed	After multiplying and adding, the result is divided by the modulus and the remainder is kept. This operation confines the computed values within a specific range (0 to Modulus – 1). The value 2,147,483,648 is 2³¹, a common choice for many LCG implementations.
7	Lower Bound	9	Change To Fit Your Needs	These parameters allow you to scale the raw output of the LCG into a more useful range.
8	Upper Bound	250	Change To Fit Your Needs	These parameters allow you to scale the raw output of the LCG into a more useful range.
9	List Count	20	Change To Fit Your Needs	This determines how many numbers (or iterations) the generator should produce. You can change this value if you need more or fewer numbers in your output list.

Sheet: Sheet1

Then I load it to the power query to LCG, and pivot column value.

Then referenced it to a new query Params. and converted this to a record, so I can use it else where as needed.

Then added a new blank query, and then adjust parameters and formula.

Here are the code with more explain for your references.

This is the code for the LCGSequence:

Formula:

Please Login or Register  to view this content.

Here are the end result:
Excel 2016 (Windows) 64 bit

	A
11	Here is the list
12	LCGSequence
13	35
14	156
15	192
16	248
17	96
18	59
19	191
20	111
21	112
22	174
23	85
24	96
25	65
26	247
27	86
28	188
29	138
30	17
31	43
32	101

Sheet: Sheet1

Thanks for your reply, and I hope this may help.

Thanks for the reputation.