Troubleshoot random numbers in Power Query

Creating random numbers in power query is not as straight forward as you might think. In this video we will cover the main issues with this:
1. The random number is the same on each row
2. How to keep the random number from changing

Enjoy!

SUBSCRIBE to learn more about Power and Excel BI!

Our PLAYLISTS:

☼☼☼☼☼☼☼☼☼☼

POWER BI COURSES:

Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:

☼☼☼☼☼☼☼☼☼☼

ABOUT CURBAL:

************

************

QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Twitter ► @curbalen, @ruthpozuelo

#CURBAL #SUBSCRIBE

[gregmccann]

Thank you for the consistently great content Ruth.

I needed to create a 4-column table of random logical values and used Julian’s solution as a starting point.
What I found was if you just copy the formula List.Random(1, [Index]) across all 4 columns you will get the same answer in each column (because it’s the same seed). So, I changed it to generate 4 random numbers, one for each column.
Column1 = List.Random(4, [Index]){0}, Column2 = List.Random(4, [Index]){1}.
Finally bumped the number to between 1-100 and used the Number.IsOdd to change the value to logical Number.IsOdd(List.Random(4, [Index]){0}*100+1).
It worked a treat.

[JackOfTrades]

I can't believe you're covering this! I was just doing random numbers last Friday and ran into this issue. I resorted to using a DAX calc column using RANDBETWEEN.
Thanks for this video!

[JohnLee-dpey]

Hi, I wanna change the type of the random number column to decimal numbers, but if I do that, every row will have the same value. Is it possible to keep the value of the generated numbers while changing the type?

[juliantagell]

Found a solution: Step 1. Add an index column, Step 2. Add a Custom column containing List.Random(1, [Index]), Step 3 Expand the List in the custom column. And your good to go. The random number won't update any more :-)

[barttitulaerexcelbart]

Hello Ruth, thank you for adressing this topic. Unfortunately it did not work for me. I uses both Excel and Power BI. I added an index column, used randbetween, just as in your example. But then, the column " custom" should be determined as number (whole or decimal). And then all the rows have the same number. In your video you did also not this last step. Could you please continue this example? Thank you. Bart