Generate Random samples per category

Hi ManojG ,

Here’s a simple approach, Load your full case list into Power Query (Home > Transform Data). 

If you don’t already have a “Week” column, create one (you can use “Add Column” > “Week of Year” or set up your own custom logic if your weeks are defined differently). To pick one random sample per user per week:

Add a new column with a random number (Add Column > Custom Column, and enter: Number.RandomBetween(0, 999999)). Sort by User, Week, and the random number. For each User + Week, keep only the first row (use “Group By” > “All Rows”, then keep the top row in each subtable). 

If you need to avoid picking the same case twice for a user across weeks: After each week’s selection, keep a running list of already-picked cases and exclude them in the next week’s pick. You can do this stepwise in Power Query, or use a parameter table if you want to “lock” the selected samples.

If new users are added, this method will automatically start picking for them from their first week.