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 IRe-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.