Generate Random samples per category

[ManojG]

Hello PBI community members,

Hope you are doing well!

I need to select in total 4 random cases per user per month for audit. I get the data dump every week. In first week, say I have three users in the list- A, B and C so I'll pick one sample each for every user. Next week, another user D added to the list, so I'll have to pick one case for users A, B and C and two cases for user D(D is newly added user, was not audited previous week). In third week, no change in user list, it's same as previuos week-A,B,C and D and I'll pick one sample each for week three. In fourth week, a new user E is added to the list so i'll pick one sample each for A,B,C and D and 4 for E. This gives me 4 sample for all 5 users A,B,C,D and E.

Please help me with solving this in Power BI. I have attached the sscreen shot of sample datasett. Case numbers are unique.

Thanks in advance.

[Balakrishnan_J]

Re-Post:
Hi @ManojG,

Hope you are doing well.
Please find the below M-Code. Copy and Paste in advanced editor of power query.

let
// Assuming you kept files in folder and created source like this.
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddNBDoMgEIXhu7g2KQyMwLJgT2G8/zWKFUjj4+0MX5j8gh7H8l7WxVoR5+pDePmXGNHlXP/EU9Gn5LpwzfKBSqSSmKh5SqkLdYdXKBiyUYG2IdB2vanzrk+zdkKBUwTKjTbl5Dh5oNIJCwdh4SAsHJSA9kbBcLKcZHpQ9WxDq4goCSTfEjcqAaTckhzI3sRTUSpY8PsxZHwuohMKQLlRu/gp4cDSCQd2apcxJQHaO+HAQQr06RQ5JUrRcML4QY/48ws=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [User = _t, CaseNumber = _t, Date = _t]),

Week_Count = List.Count(List.Distinct(Source[Date])),

Grouping_Date = Table.Group(Source, {"User"}, {{"AllRows", each _}}),

Iteration_Logic = Table.TransformColumns(Grouping_Date, {"AllRows", (x) =>
let
ListCount = List.Count(List.Distinct(x[Date]))
in
if ListCount = Week_Count then Table.Distinct(x, {"Date"}) else
if ListCount = 1 then Table.FirstN(x, Week_Count) else
Table.FirstN(Table.Distinct(x, {"Date"}) & Table.Skip(x, 1), Week_Count)
}),

Result = Table.Combine(Iteration_Logic[AllRows])
in
Result

If this post helps, accept it as solution and give kudos.

[V-yubandi-msft · Accepted solution]

Hi @ManojG ,

Thank you for contacting the Fabric Community.

@jgeddes , response matches your requirements based on the details you shared. Please review the suggested solution and let us know if any changes are needed.

@jgeddes , We also appreciate your clear explanation and the attached PBIX file, which helped us understand  scenario.

Best regards,
Community Support Team – Yugandhar

[Balakrishnan_J]

Hi @ManojG ,

Copy and Paste the below M-code in advanced editor of power query.
I used grouping technique to solve this problem. Hope it solve your issue. Apply same logic if it works. 

let
Source = Table.FromRecords({
[User="A", CaseNumber=1, Date=#date(2025, 1, 1)],
[User="A", CaseNumber=2, Date=#date(2025, 1, 1)],
[User="A", CaseNumber=4, Date=#date(2025, 8, 1)],
[User="B", CaseNumber=5, Date=#date(2025, 8, 1)],
[User="B", CaseNumber=6, Date=#date(2025, 8, 1)],
[User="B", CaseNumber=7, Date=#date(2025, 1, 15)],
[User="C", CaseNumber=8, Date=#date(2025, 1, 15)],
[User="C", CaseNumber=9, Date=#date(2025, 1, 15)]
}),

Week_Count = List.Count(List.Distinct(Source[Date])),

Grouping = Table.Group(Source, {"User"}, {{"AllRows", each _}}),

Iteration_Logic = Table.TransformColumns(Grouping, {"AllRows", (x) =>
let
ListCount = List.Count(List.Distinct(x[Date]))
in
if ListCount > 1 then Table.Distinct(x, {"Date"}) else Table.FirstN(x, Week_Count)
}),

Result = Table.Combine(Iteration_Logic[AllRows])
in
Result

[jgeddes]

Here is another method to try. I have attached the pbix file as the explaination is too long for a post.
It will look for new users in a week and determine the number of samples to pull for that user based on the week. 
End result...

You can amend it to fit your needs as you see fit. 
It assumes that the number of cases added for a user is greater or equal to the number of samples that would be required. And, you may see duplicate selected cases. You can use the 'refresh preview' button to cycle the requests until you get unique numbers.

It assumes that the week data is coming is as separate queries.

let
    reportWeekStart = 
    Date.WeekOfYear(#date(2025,07,04)),
    combineWeeks = 
    Table.Combine({WeekOne, WeekTwo, WeekThree, WeekFour}),
    add_week_of_year = 
    Table.AddColumn(
        combineWeeks, 
        "WeekOfYear", 
        each Date.WeekOfYear([Date]), 
        Int64.Type
    ),
    group_user_week = 
    Table.Group(
        add_week_of_year, 
        {"User", "WeekOfYear"}, 
        {{"Count", each _[CaseNumber], type list}}
    ),
    add_report_week = 
    Table.AddColumn(
        group_user_week, 
        "ReportWeek", 
        each 1 + ([WeekOfYear] - reportWeekStart), 
        Int64.Type
    ),
    Source = 
    Table.Group(
        add_report_week, 
        {"ReportWeek"}, 
        {{"Count", each _[User], type list}}
    ),
    add_new_user_list = 
    Table.AddColumn(
        Source, 
        "Custom", 
        each 
            try List.Difference([Count], Table.SelectRows(Source, (x)=> x[ReportWeek] = [ReportWeek] -1)[Count]{0}) 
            otherwise {}, 
        type list
    ),
    add_user_sample_count = 
    Table.AddColumn(
        add_new_user_list, 
        "CombinedList", 
        each 
        let
        repeatedList = 
            List.Zip(
                {
                    List.Difference([Count], [Custom]), 
                    List.Numbers(1,List.Count(List.Difference([Count], [Custom])),0)
                }
            ),
        newList = 
            List.Zip(
                {
                    [Custom],
                    List.Numbers([ReportWeek], List.Count([Custom]), 0)
                }
            )
        in 
        List.Combine({repeatedList, newList}),
        type list
    ),
    expand_user_sample_count = 
    Table.ExpandListColumn(
        add_user_sample_count, 
        "CombinedList"
    ),
    extract_values = 
    Table.TransformColumns(
        expand_user_sample_count, 
        {"CombinedList", each Text.Combine(List.Transform(_, Text.From), ","), type text}
    ),
    split_sample_count_to_columns = 
    Table.SplitColumn(
        extract_values, 
        "CombinedList", 
        Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), 
        {"User", "Samples"}
    ),
    remove_columns = 
    Table.RemoveColumns(
        split_sample_count_to_columns,
        {"Count", "Custom"}
    ),
    add_user_cases = 
    Table.AddColumn(
        remove_columns, 
        "Cases", 
        each 
        let 
            week = [ReportWeek], 
            user = [User] 
        in 
            Table.SelectRows(
                add_report_week, 
                each [ReportWeek] = week and [User] = user)[Count]{0}, 
        type list
    ),
    set_types = Table.TransformColumnTypes(add_user_cases,{{"Samples", Int64.Type}}),
    create_random_list = 
    Table.AddColumn(
        set_types, 
        "RandomPosition", 
        each 
            let 
                samples = [Samples], 
                caseCount = List.Count([Cases])-1 
            in 
                List.Generate(
                    ()=> 0, 
                    each _ < samples, 
                    each _ + 1, 
                    each Number.Round(Number.RandomBetween(0, caseCount), 0)), 
            type list
        ),
    select_random_cases = 
    Table.AddColumn(
        create_random_list, 
        "RandomSelectedCase", 
        each 
        let 
            caseList = [Cases] 
        in 
            List.Transform(
                [RandomPosition], 
                each caseList{_}
            ), 
        type list
    ),
    select_needed_columns = 
    Table.RemoveColumns(
        select_random_cases,
        {"Samples", "Cases", "RandomPosition"}
    ),
    expand_sample_cases = 
    Table.ExpandListColumn(
        select_needed_columns, 
        "RandomSelectedCase"
    )
in
    expand_sample_cases

[rohit1991]

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.