Forum Discussion
Generate Random samples per category
- 1 year ago
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
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