Selecting Random set of Records from a Dataset

[orourkebp]

Hello all,

 New Power BI user.

Tried some examples found on the web but I cannot seem to apply them to this scenario.

I have a dataset of purchases for various branches around the United States.  I am looking to extract a percentage of them for auditing purposes.  More precisely, a percentage by state.  For example:

The states have the following number of records:

Alabama 1000 

Nebraska 250 

California 3750 

I want to extract 10% of the records.  So

100 of Alabama

25 of Nebraska

375 for California

I have tried creating a column using Number.Random & Number.RandomBetween but every record gets the same random number so I can't use it to select say "the top 10%".

Any guidance would be greatly appreciated.

[Greg_Deckler]

Perhaps look at Table.Range

https://msdn.microsoft.com/en-us/library/mt260780.aspx

Also, maybe Table.SelectRows and provide a condition that the row index mod 10 is 0?

https://msdn.microsoft.com/en-us/library/mt260810.aspx

[orourkebp]

Thank you smoupre.

These have promise.  But I need to assign a random value to each record first.  I think I need to create a new column and assign a random value to each record.  Tried using Number.Random but it provides the same value to each row.  Once, I have the values assigned your suggestion will be a good next step so I appreciate that.  I may have to go the DAX route.

Appreciate your help.

[v-caliao-msft · Accepted solution]

@orourkebp,

RAND function return a random value for each row. Please refer the screenshot below.

Regards,

Charlie Liao

[orourkebp]

Thank you, Charlie.

I was actually looking for a solution to part of the query step which is probably not the correct way of thinking.  

I appreciate your feedback.

Brian

[OwenAuger]

Have a look at this post, in particular Colin Banfield's reply:

https://social.technet.microsoft.com/Forums/en-US/81ca82f3-fa9f-4e49-935b-33b42b257e41/random-number...

[Greg_Deckler]

Hmm, you could do this in DAX if you perhaps have a query by state and then create a calculated table where you grab a number of records less than some index. So, in Alabama create a calculated table of and index <= 100. Kind of hacky. Will try to think of a more elegant solution.