Randomize PQ Results Based on Certain Criteria

[seaottr]

Hi Power Query Gurus out there!

I don't have extensive knowledge of Power Query, but use it for simpler tasks. I'm currently using it to query data from a SharePoint list (not relevant to my question), and I'm looking to have PQ look at the data (sample data below), and randomly select 3 IDs for each Employee/Manager.

Based on the data set below, that should produce 12 random IDs for "John Smith" (specifically 3 for each of John's employees), 12 random IDs for "Mary Brown" (specifically 3 for each of Mary's employees), and 12 random IDs for "Lisa Simpson" (specifically 3 for each of Lisa's employees).

I know I could also do this with VBA, but I'm looking for the PQ query to do everything all with one refresh.

Any help would be greatly appreciated for this Power Query idiot

Manager	Employee	ID
John Smith	Employee 1	2429928​
John Smith	Employee 2	337073​
John Smith	Employee 4	33930317​
John Smith	Employee 4	25263840​
John Smith	Employee 1	701579​
John Smith	Employee 3	967994​
John Smith	Employee 2	198319429​
John Smith	Employee 3	5197473​
John Smith	Employee 4	539053​
John Smith	Employee 3	611529​
John Smith	Employee 4	3091981​
John Smith	Employee 1	99859​
John Smith	Employee 1	21671976​
John Smith	Employee 2	150127189​
John Smith	Employee 4	613581​
John Smith	Employee 2	25306513​
John Smith	Employee 1	15565530​
John Smith	Employee 2	649545​
John Smith	Employee 3	426138​
John Smith	Employee 3	183305801​
Mary Brown	Employee 4	1831019​
Mary Brown	Employee 2	484174​
Mary Brown	Employee 3	442733​
Mary Brown	Employee 4	2595118​
Mary Brown	Employee 4	396377​
Mary Brown	Employee 4	53598054​
Mary Brown	Employee 1	154059591​
Mary Brown	Employee 1	133444​
Mary Brown	Employee 4	28395377​
Mary Brown	Employee 3	22363651​
Mary Brown	Employee 2	661402​
Mary Brown	Employee 1	870077​
Mary Brown	Employee 3	117759350​
Mary Brown	Employee 2	1327183​
Mary Brown	Employee 3	888435​
Mary Brown	Employee 1	903​
Mary Brown	Employee 3	1899601​
Mary Brown	Employee 2	437726​
Mary Brown	Employee 2	19303604​
Mary Brown	Employee 1	12196487​
Lisa Simpson	Employee 2	707090​
Lisa Simpson	Employee 1	15316272​
Lisa Simpson	Employee 2	25814310​
Lisa Simpson	Employee 3	470464​
Lisa Simpson	Employee 1	515186​
Lisa Simpson	Employee 3	35011322​
Lisa Simpson	Employee 4	1920190​
Lisa Simpson	Employee 2	195898​
Lisa Simpson	Employee 3	217373​
Lisa Simpson	Employee 1	1362011​
Lisa Simpson	Employee 4	115747​
Lisa Simpson	Employee 2	7000976​
Lisa Simpson	Employee 3	143910400​
Lisa Simpson	Employee 1	762190​
Lisa Simpson	Employee 4	15538251​
Lisa Simpson	Employee 2	26335404​
Lisa Simpson	Employee 1	729790​
Lisa Simpson	Employee 3	346976​
Lisa Simpson	Employee 4	62082228​
Lisa Simpson	Employee 4	5923133​

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl = Table.Group(Source, {"Manager", "Employee"}, {{"All", each Table.Buffer(Table.AddColumn(Table.SelectColumns(_, {"ID"}), "Random", (x)=> Number.Random() ))}}),
    tbl2 = Table.TransformColumns(tbl, {"All", each Table.FirstN(Table.Sort(_, {"Random"}),3)}),
    Result = Table.ExpandTableColumn(tbl2, "All", {"ID"})
in
    Result

 

[seaottr]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl = Table.Group(Source, {"Manager", "Employee"}, {{"All", each Table.Buffer(Table.AddColumn(Table.SelectColumns(_, {"ID"}), "Random", (x)=> Number.Random() ))}}),
    tbl2 = Table.TransformColumns(tbl, {"All", each Table.FirstN(Table.Sort(_, {"Random"}),3)}),
    Result = Table.ExpandTableColumn(tbl2, "All", {"ID"})
in
    Result

Thank you so much for the reply JGordon! I haven't yet had a chance to try this out as I was done work for the weekend when you posted, and it's shaping up to be a crazy week!

I'll definitely try this out though and let you know how it goes!

Thanks again!

 

[seaottr]

Just trying this out now...

 

[seaottr]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl = Table.Group(Source, {"Manager", "Employee"}, {{"All", each Table.Buffer(Table.AddColumn(Table.SelectColumns(_, {"ID"}), "Random", (x)=> Number.Random() ))}}),
    tbl2 = Table.TransformColumns(tbl, {"All", each Table.FirstN(Table.Sort(_, {"Random"}),3)}),
    Result = Table.ExpandTableColumn(tbl2, "All", {"ID"})
in
    Result

This works PERFECTLY JGordon! Thank you again for your help! Quick question, how would I modify this to still include other columns in the original data source?

 

[JGordon11]

This works PERFECTLY JGordon! Thank you again for your help! Quick question, how would I modify this to still include other columns in the original data source?

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tcn = List.RemoveItems(Table.ColumnNames(Source), {"Manager", "Employee"}),
    tbl = Table.Group(Source, {"Manager", "Employee"}, {{"All", each Table.Buffer(Table.AddColumn(Table.SelectColumns(_, tcn), "Random", (x)=> Number.Random() ))}}),
    tbl2 = Table.TransformColumns(tbl, {"All", each Table.FirstN(Table.Sort(_, {"Random"}),3)}),
    Result = Table.ExpandTableColumn(tbl2, "All", tcn)
in
    Result