cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Power Query: Custom Column Not Sorting

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!

1 ACCEPTED SOLUTION
Advocate V
Advocate V

Hi @syrah_rose,

 

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

View solution in original post

3 REPLIES 3
Advocate V
Advocate V

Hi @syrah_rose,

 

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

View solution in original post

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. 

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

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors