Power Query: Custom Column Not Sorting

[Anonymous]

Hi everyone,

I am trying to assign each row of my dataset with a random number and then sort in either an ascending or descending order based upon that random number, essentially shuffling the rows in a random order. I have used this article as a guide (https://chandoo.org/wp/power-query-random-sample/) but when I select sort on my new random number column, it will sort the random numbers but the rest of the rows do not sort with the random number. Instead the random numbers sort independently of the rest of the columns. Any ideas? I'm open to using the RAND() function but I can't figure out how to keep just the top N numbers for my sample in that way. Thanks in advance for your help!

[gpoggi · Accepted solution]

Hi @Anonymous,

You can get it fixed with a little trick, let's say your Random Column is named [Random_Value] ok then simply add a new custom column as [NewRandom_Value] and put [Random_Value] * 100, then sort this new column [NewRandom_Value] (Ascending or Descending, as you wish), then delete those 3 columns (Index, Random_Value, NewRandom_Value) and bam! now after each preview refresh you will get a different top 100 sample from your dataset.

Let me know if this helps.

If you have any question, just let me know.

Regards,

Gian Carlo Poggi

[Anonymous]

Hi,

Oh perfect, thanks for the reply! Can you help me understand why this happened in the first place? I've done this before about a month ago and I don't remember having this issue. 

[gpoggi]

Well, if you check it out, when you attempt to sort the original random column, in fact it doesn't get sorted, it is only regenerated, probably because of M language nature, Number.Random is a very sensitive function, for example before deleting your Random_Value column if you add a new column you will see that Random_Value column will get generated again and this time all rows are getting the same random value :S

So let's say this is one of the weird functions from M language that should be treated carefully.

Regards,

Gian Carlo Poggi