Forum Discussion
Generate Random samples per category
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
Helper IHi 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